UAK May-September 2023

SQL

SET @GMT = '+00:00';
SET @Chile_time_zone = '-04:00'; 

select page.page_title as Page, 
  count(*) over (partition by page_title) as page_count,
  actor_name as Account, 
  rev_timestamp as UTC,
  @lt:=CONVERT_TZ(rev_timestamp, @GMT, @Chile_time_zone) as local_time,
  DAYNAME(@lt) as Day,
  DATE_FORMAT(@lt, "%Y-%m-%d") as yymmdd,
  DATE_FORMAT(@lt, "%H:%i") as hhmm
  from actor
  join revision on actor.actor_id = revision.rev_actor
  join page on revision.rev_page = page.page_id
  
 where 
 revision.rev_timestamp > "202305010000" and
 actor_id in (
225207955,
225208061,
225151920,
225622151,
225520474,
225567656,
225536499,
225520413,
226098950,
225256425,
225485466,
225985323,   
 224032647,
 226068210,
 224032514,
 224032424,
 223982659,
 225042734,
 225622088,
 226178810,
 225934251,
 226099029,
 225736157,
 222050989,
 221832606,
 225736189,
 226098998,
 225768452,
 225985394,
 225768379,
 225751743,
 225736229,
 225768346,
 225464996,
 225934025,
 225536546,
 225567758,
 225622025,
 225665732,
 222035019,
 226223022,
 225751697,
 226178731,
 225288069,
 226178769,
 225952239,
 225736265,
 225042813,
 225768419,
 226098974,
 226328654,
 225272900,
 225314656,
 225536598,
 225567721,
 225388909,
 225359094,
  # August starts here
226374429,
226374465,
226374562,
226374508,
226381021,
226381002,
 226395580,
 226424609,
 226450072,
 226450088,
 226450112,
 226450151,
 226450202,
 226533335,
 226533380,
 226533401,
 226533431,
 226533471,
 226328554,
   # September starts here
   226697214,
   226697240,
   226697269
 )     
 

 order by DAYOFWEEK(local_time), hhmm
 limit 500;
SQL

Checking query status...
