postgresql - SQL Reactivation Revenue -

postgresql - SQL Reactivation Revenue -

i'm looking query sum reactivation revenue given date on. have next query;

select advertisable, extract(year day), extract(month day), round(sum(cost)/1e6) adcube dac advertisable_eid in (select advertisable adcube dac grouping advertisable having sum(cost)/1e6 > 100) grouping advertisable, extract(year day), extract(month day) order advertisable, extract(year day), extract(month day)

from export excel , check accounts thay have stopped spending 4 months , reactivated. track new revenue new reactivtion month.

is possible sql query without need of excel?

thanks

assuming 4 months nowadays in data, can using window functions. can find n things in row taking difference between 2 row_numbers(). here idea:

with t ( select advertisable, extract(year day) yy, extract(month day) mon, round(sum(cost)/1e6) val adcube dac advertisable_eid in (select advertisable adcube dac grouping advertisable having sum(cost)/1e6 > 100 ) grouping advertisable, extract(year day), extract(month day) ) select advertisable, min(yy * 10000 + mon) yyyymm (select t.*, (row_number() on (partition advertisable order yy, mon) - row_number() on (partition advertisable, val order yy, mon) ) grp t ) grouping advertisable, grp, val having count(*) >= 4 , val = 0;

sql postgresql