CODEWITHSUNDEEP
sqlgoogle-cloud-platformgoogle-bigqueryanalytics
Dobrin Stoilov
Dobrin Stoilov

Reputation: 9

Is a recursive cte the solution? Any ideas for a solution here?

I'd really appreciate help on this one.

I need to keep rows with IDs: (1, 4, 6) from this table by implementing the following logic.

Here's the logic: the first sale is always selected. Then, I need to select the next sale where the sale_date is at least 6 months after the previous selected one (row1).

And here's the tricky part - it's kind of a recursion. I need to select every row with a sale_date that is at least 6 months after the previous SELECTED row (NOT previous row from the raw data).

That's why ID = 4 should be selected - it's > 6 months after the previous selected row (ID = 1), and it doesn't matter that it isn't 6 months after ID = 3 as this isn't selected.

ID = 6 is selected because it's > 6 mo after the previous selected row (ID = 4). The table is just an example, it will grow with adding more rows with sales (and salespeople and clients).

I hope I was clear with the explanation. I think recursion would be useful here; I tried but didn't manage to make it work/ ANy help would be appreciated!

enter image description here

Upvotes: -1

Views: 70

Answers (2)

Mikhail Berlyant
Mikhail Berlyant

Reputation: 173161

Consider below approach

WITH RECURSIVE temp AS (
  SELECT *, ROW_NUMBER() OVER(PARTITION BY client, salesperson ORDER BY sale_date) pos
  FROM your_table 
), result AS (
  SELECT *, sale_date AS first_date, TRUE AS new_group 
  FROM temp WHERE pos = 1
  UNION ALL
  SELECT t.*, 
    IF(DATE_DIFF(t.sale_date, r.first_date, month) > 6, t.sale_date, r.first_date), 
    IF(DATE_DIFF(t.sale_date, r.first_date, month) > 6, TRUE, FALSE)
  FROM temp t JOIN result r 
  ON (t.pos, t.client, t.salesperson)  = (r.pos + 1, r.client, r.salesperson)
)
SELECT id, client, salesperson, sale_date
FROM result
WHERE new_group
ORDER BY sale_date

if applied to sample data in your question. - output is

output

Upvotes: 0

ValNik
ValNik

Reputation: 5916

You can not use window functions in recursive part of query.
You can prepare previous sale date (prev_sale_date) and use this column in join condition in recursion.

See example

with recursive  
saleRanges as(
  select * 
    ,coalesce((lag(sale_date)over(partition by CLient order by sale_date) )
        ,sale_date) as prev_sale_date
  from data
)
,r as(
  select id,Client,SalesPerson,sale_date,prev_sale_date
  from saleRanges
  where Client='Cl1' and sale_date=(select min(sale_date) from data where Client='Cl1')
  union all
  select t.id,t.Client,t.SalesPerson,t.sale_date,t.prev_sale_date
  from r inner join saleRanges t on t.Client=r.Client 
      and t.sale_date>date_add(r.sale_date,interval 6 month)
      and t.prev_sale_date<=date_add(r.sale_date,interval 6 month)

)
select * from r order by Client,sale_date;
id client salesperson sale_date prev_sale_date
1 Cl1 Jack 2023-01-01 2023-01-01
4 Cl1 Jack 2023-09-01 2023-06-01
6 Cl1 Jack 2024-04-01 2023-11-01

Upvotes: 0

Related Questions