Tewiki: Top 100 users by bytes added in 2020

SQL

SELECT concat(substr(rev_timestamp,1,4),"-",substr(rev_timestamp,5,2),"-",
			substr(rev_timestamp,7,2)) సృష్టించిన_తేదీ, p1.page_title పేజీ_పేరు, actor_name వాడుకరిపేరు, count(p1.page_id),
			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,actor
left join actor ON actor_id = rev.rev_actor
left JOIN revision parent
ON parent.rev_id = rev.rev_parent_id
WHERE page_namespace = 0
    and substr(rev.rev_timestamp,1,4)="2020"
    AND actor_user IS NOT NULL

-- where p2.page_title=p1.page_title
-- and p2.page_namespace=1
-- and p1.page_namespace=0

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

Checking query status...
