Untitled query #88939

This query is marked as a draft This query has been published by A smart kitten.

attempt to get unblocks per month
(as a raw number, and as a % of blocks made in that month? maybe?)

SQL

select 
  dec24_unblocks                       as 'dec24 so far',
  100 * dec24_unblocks / dec24_blocks  as 'dec24so far (as % of blocks)',
  nov24_unblocks                       as 'nov24',
  100 * nov24_unblocks / nov24_blocks  as 'nov24 (as % of blocks)',
  oct24_unblocks                       as 'oct24',
  100 * oct24_unblocks / oct24_blocks  as 'oct24 (as % of blocks)',
  sep24_unblocks                       as 'sep24',
  100 * sep24_unblocks / sep24_blocks  as 'sep24 (as % of blocks)',
  aug24_unblocks                       as 'aug24',
  100 * aug24_unblocks / aug24_blocks  as 'aug24 (as % of blocks)',
  jul24_unblocks                       as 'jul24',
  100 * jul24_unblocks / jul24_blocks  as 'jul24 (as % of blocks)',
  jun24_unblocks                       as 'jun24',
  100 * jun24_unblocks / jun24_blocks  as 'jun24 (as % of blocks)',
  may24_unblocks                       as 'may24',
  100 * may24_unblocks / may24_blocks  as 'may24 (as % of blocks)',
  apr24_unblocks                       as 'apr24',
  100 * apr24_unblocks / apr24_blocks  as 'apr24 (as % of blocks)',
  mar24_unblocks                       as 'mar24',
  100 * mar24_unblocks / mar24_blocks  as 'mar24 (as % of blocks)',
  feb24_unblocks                       as 'feb24',
  100 * feb24_unblocks / feb24_blocks  as 'feb24 (as % of blocks)',
  jan24_unblocks                       as 'jan24',
  100 * jan24_unblocks / jan24_blocks  as 'jan24 (as % of blocks)'
from (
  select
    sum(case when log_action = 'unblock' and log_timestamp > '202412' then 1 else 0 end)                    as dec24_unblocks,
    sum(case when log_action = 'block' and log_timestamp > '202412' then 1 else 0 end)                      as dec24_blocks,
    sum(case when log_action = 'unblock' and log_timestamp between '202411' and '202412' then 1 else 0 end) as nov24_unblocks,
    sum(case when log_action = 'block' and log_timestamp between '202411' and '202412' then 1 else 0 end)   as nov24_blocks,
    sum(case when log_action = 'unblock' and log_timestamp between '202410' and '202411' then 1 else 0 end) as oct24_unblocks,
    sum(case when log_action = 'block' and log_timestamp between '202410' and '202411' then 1 else 0 end)   as oct24_blocks,
    sum(case when log_action = 'unblock' and log_timestamp between '202409' and '202410' then 1 else 0 end) as sep24_unblocks,
    sum(case when log_action = 'block' and log_timestamp between '202409' and '202410' then 1 else 0 end)   as sep24_blocks,
    sum(case when log_action = 'unblock' and log_timestamp between '202408' and '202409' then 1 else 0 end) as aug24_unblocks,
    sum(case when log_action = 'block' and log_timestamp between '202408' and '202409' then 1 else 0 end)   as aug24_blocks,
    sum(case when log_action = 'unblock' and log_timestamp between '202407' and '202408' then 1 else 0 end) as jul24_unblocks,
    sum(case when log_action = 'block' and log_timestamp between '202407' and '202408' then 1 else 0 end)   as jul24_blocks,
    sum(case when log_action = 'unblock' and log_timestamp between '202406' and '202407' then 1 else 0 end) as jun24_unblocks,
    sum(case when log_action = 'block' and log_timestamp between '202406' and '202407' then 1 else 0 end)   as jun24_blocks,
    sum(case when log_action = 'unblock' and log_timestamp between '202405' and '202406' then 1 else 0 end) as may24_unblocks,
    sum(case when log_action = 'block' and log_timestamp between '202405' and '202406' then 1 else 0 end)   as may24_blocks,
    sum(case when log_action = 'unblock' and log_timestamp between '202404' and '202405' then 1 else 0 end) as apr24_unblocks,
    sum(case when log_action = 'block' and log_timestamp between '202404' and '202405' then 1 else 0 end)   as apr24_blocks,
    sum(case when log_action = 'unblock' and log_timestamp between '202403' and '202404' then 1 else 0 end) as mar24_unblocks,
    sum(case when log_action = 'block' and log_timestamp between '202403' and '202404' then 1 else 0 end)   as mar24_blocks,
    sum(case when log_action = 'unblock' and log_timestamp between '202402' and '202403' then 1 else 0 end) as feb24_unblocks,
    sum(case when log_action = 'block' and log_timestamp between '202402' and '202403' then 1 else 0 end)   as feb24_blocks,
    sum(case when log_action = 'unblock' and log_timestamp between '202401' and '202402' then 1 else 0 end) as jan24_unblocks,
    sum(case when log_action = 'block' and log_timestamp between '202401' and '202402' then 1 else 0 end)   as jan24_blocks
  from logging
  where log_timestamp > '2024'
  and log_type = 'block'
  and log_action regexp '^(un)?block$'
) as subquery;

By running queries you agree to the Cloud Services Terms of Use and you irrevocably agree to release your SQL under CC0 License.

Submit Query Stop Query

All SQL code is licensed under CC0 License.

SQL

Checking query status...