german - Quarry

SQL

WITH r as (
  SELECT rev_page, substring(rev_timestamp, 1, 8) date
  FROM revision
  WHERE rev_timestamp BETWEEN '20220601000001' and '20220602000001'
  GROUP BY 1, 2
)

SELECT  r.rev_page, page.page_title, r.date,  
		sum(case when f.rev_timestamp BETWEEN '20220531000001' and '20220601000001' then 1 else 0 end) same_day_revs, 
		sum(case when f.rev_timestamp BETWEEN '20220530000001' and '20220531000001' then 1 else 0 end) prev_day_revs, 
		sum(case when f.rev_timestamp BETWEEN '20220525000001' and '20220531000001' then 1 else 0 end) week_before_revs, 
		sum(case when f.rev_timestamp BETWEEN '20220502000001' and '20220531000001' then 1 else 0 end) month_before_revs, 
		sum(case when f.rev_timestamp BETWEEN '20210601000001' and '20220531000001' then 1 else 0 end) year_before_revs, 
		sum(case when f.rev_timestamp BETWEEN '20220531000001' and '20220608000001' then 1 else 0 end) week_after_revs, 
		sum(case when f.rev_timestamp BETWEEN '20220601000001' and '20220602000001' then 1 else 0 end) day_after_revs, 
		sum(case when f.rev_timestamp BETWEEN '20220602000001' and '20220603000001' then 1 else 0 end) day_after_2_revs
				
FROM  r
	LEFT JOIN revision f on r.rev_page = f.rev_page
    						and f.rev_timestamp BETWEEN '20210601000001' and '20220608000001' 
    LEFT JOIN page on page.page_id = r.rev_page
    
  GROUP BY 1, 2
SQL

Checking query status...
