There is a table of customers with purchases made in a certain month. It happens that a customer did not buy anything in a certain month, so this month is skipped.
Task:
I need to extract only those lines where the customer has been shopping continuously every month.
I tried to create a flag, but I don’t understand how I can remove the first row of the table. It turned out to be done using group and having, but I would like to see a shorter query.
The DBMS used is PostgreSQL.
with cte as (
select
client_id,
date_trunc('month', dt)::date as m_date,
sum(liters) as s_lit
from azs
group by client_id, m_date
order by 1, 2
),
cte2 as (
SELECT client_id, m_date,
CASE WHEN DATE_PART('month', m_date) - DATE_PART('month', lag(m_date) over (partition by client_id order by m_date)) IS NULL THEN 1
WHEN DATE_PART('month', m_date) - DATE_PART('month', lag(m_date) over (partition by client_id order by m_date)) > 1 THEN 1
ELSE 0
END AS flag
FROM cte
)
select * from cte2
2
Answers
You can use a
common table expression (CTE)
to first group the data by client and month, then use a window function to identify any gaps in monthly purchases and then filter out any customers with gaps. Try thisI believe it will be hard to avoid a group-by for this task, but it can be done with a single WITH-clause before the group-by.
In the below query, client_id=1 is missing orders in March, client_id=3 is missing orders in February, and client_id=4 has a single order in March. Hence expected output is client_id 2 and 4.
Query is testet with Postgres playground.
Output