I have a query like so:
select distinct(line_items.email) as email, count(orders.order_id) over(partition by orders.order_id) as num_orders from line_items inner join orders on line_items.order_id=orders.order_id
This is apart of a broader query where I need to be able to select from line_items vs. orders.
The problem here is that:
- A user has multiple line items on a specific order (bought 2 different products)
- The window function sees two rows and is still counting twice
I can't do
count distinct because Redshift doesn't support count distinct as window function.
The records looks something like this:
line_item_id,order_id,email 123,1,email@example.com 124,1,firstname.lastname@example.org
The result of the above query looks like this:
Is there a way I can get this to be:
While retaining use of the window function?