1 year >10 - Quarry

SQL

# #len of article (total - month - week - day before, same), page namespace

# revisions
SELECT page.page_title, page.page_id, 
			page.page_len as page_len_today, 
            min(revision.rev_timestamp) as created_at,
            count(revision.rev_id) as count_rev_today,
            sum(case when revision.rev_timestamp<='20220102000000' then 1 else 0 end) as count_rev_before_event_day,
            sum(case when revision.rev_timestamp='20220102000000' then 1 else 0 end) as count_rev_event_day,   # rev_minor_edit
            sum(case when revision.rev_timestamp BETWEEN ('20220102000000'- INTERVAL 1 WEEK)  AND '20220102000000' then 1 else 0 end) as count_rev_event_week_before, 
            sum(case when revision.rev_timestamp BETWEEN ('20220102000000'- INTERVAL 1 MONTH)  AND '20220102000000' then 1 else 0 end) as count_rev_event_month_before,
            
            sum(case when revision.rev_timestamp BETWEEN '20220102000000'  AND ('20220102000000'+ INTERVAL 1 WEEK) then 1 else 0 end) as count_rev_event_week_after, 
            sum(case when revision.rev_timestamp BETWEEN '20220102000000'  AND ('20220102000000'- INTERVAL 1 MONTH) then 1 else 0 end) as count_rev_event_month_after, 
            
            sum(case when revision.rev_timestamp<='20220102000000' then revision.rev_len else 0 end) rev_len_before_event_day,
            avg(case when revision.rev_timestamp<='20220102000000' then rev_minor_edit end) prop_minor_before_event_day
FROM page	
    LEFT JOIN revision on revision.rev_page = page.page_id 
WHERE page_title ='Prime_Minister_of_Haiti'  
		and page_namespace=0
GROUP BY 1,2,3



#max(categorylinks.cl_to) categorylinks_today,
#LEFT JOIN categorylinks ON categorylinks.cl_from=page.page_id 

# # internal links 
# SELECT pl_from
# FROM pagelinks
# LIMIT 100;

# #number of external links 
# SELECT el_from
# FROM externallinks
# LIMIT 100;

 #'Marshall_Fire'????
SQL

Checking query status...
