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 protected]
124,1,[email protected]
The result of the above query looks like this:
email,num_orders
[email protected],2
Is there a way I can get this to be:
email,num_orders
[email protected],1
While retaining use of the window function?