Tewiki: Top 100 users by bytes added in 2020

SQL

SELECT concat(substr(rev.rev_timestamp,1,4),"-",substr(rev.rev_timestamp,5,2),"-",
			substr(rev.rev_timestamp,7,2)) సృష్టించిన_తేదీ, p1.page_title పేజీ_పేరు, actor_name వాడుకరిపేరు, count(actor_name),
			SUM(abs(CAST(rev.rev_len as signed)-if(rev.rev_parent_id=0,0,CAST(parent.rev_len as signed)))) AS Gross_byte_count, 
		    SUM(CAST(rev.rev_len as signed)-if(rev.rev_parent_id=0,0,CAST(parent.rev_len as signed))) AS nett_byte_count

FROM page p1, page p2,revision rev
left join actor ON actor_id = rev.rev_actor
left JOIN revision parent
ON parent.rev_id = rev.rev_parent_id
WHERE p1.page_namespace = 0
    and substr(rev.rev_timestamp,1,6) in("202308","202309","202310","202311")
    AND actor_user IS NOT NULL

and p2.page_title=p1.page_title
and p2.page_namespace=1
-- and p1.page_namespace=0
-- and rev.rev_timestamp between "20230800000000" and "20231120000000"
and p2.page_id IN (
  SELECT cl_from
  FROM categorylinks
  WHERE cl_to = 'క్రికెట్_2023_ప్రాజెక్టులో_భాగంగా_విస్తరించిన_పేజీలు'
)
  and rev.rev_page=p1.page_id
--  and rev_parent_id=0
  and actor_id=rev.rev_actor
GROUP BY actor_name
order by Gross_byte_count desc
SQL

Checking query status...
