I’m trying to display first and fifth order_id for each user.
Table looks like this:
+----------+-----------+-------------------+ | Order_ID | Client_ID | Datetime | +----------+-----------+-------------------+ | 1 | 1 | YYYYMMDD HH:MM:SS | +----------+-----------+-------------------+ | 2 | 1 | YYYYMMDD HH:MM:SS | +----------+-----------+-------------------+ | 3 | 2 | YYYYMMDD HH:MM:SS | +----------+-----------+-------------------+
I wrote something like this:
select
t.client_id,
t.order_id as first_order,
t2.order_id as fifth_order,
t.datetime as first_dt,
t2.datetime as fifth_dt,
from
(
select o.client_id, o.order_id, o.datetime,
row_number() over(partition by o.client_id order by o.datetime) as rn
from "OhMyTable" as o
) as t
left join
(
select o.client_id, o.order_id, o.datetime,
row_number() over(partition by o.client_id order by o.order_id) as rn
from "OhMyTable" as o
order by o.order_id
) as t2
on t.client_id = t2.client_id
where t.rn = 1 and t2.rn = 5
But I want to see clients who made their first orders but don’t have fifths. There should be NULL, but I can’t understand how to make it. :c
Advertisement
Answer
Put the where t2.rn = 5 condition into the left join:
select
t.client_id,
t.order_id as first_order,
t2.order_id as fifth_order,
t.datetime as first_dt,
t2.datetime as fifth_dt,
from
(
select o.client_id, o.order_id, o.datetime,
row_number() over(partition by o.client_id order by o.datetime) as rn
from "OhMyTable" as o
) as t
left join
(
select o.client_id, o.order_id, o.datetime,
row_number() over(partition by o.client_id order by o.order_id) as rn
from "OhMyTable" as o
order by o.order_id
) as t2
on t.client_id = t2.client_id
and t2.rn = 5
where t.rn = 1 and t2.datetime is null