SQL
AخA
USE nlwiki_p;
SELECT
CASE
WHEN ipb_expiry = 'infinity' THEN 'infinity'
WHEN ipb_expiry-ipb_timestamp = 1000000 THEN '1 Day'
WHEN ipb_expiry-ipb_timestamp = 7000000 THEN '1 Week'
WHEN ipb_expiry-ipb_timestamp = 83000000 THEN '1 Week'
WHEN ipb_expiry-ipb_timestamp = 100000000 THEN '1 Month'
WHEN ipb_expiry-ipb_timestamp > 9999999999 THEN CONCAT( (ipb_expiry-ipb_timestamp) / 10000000000, ' year(s)' )
ELSE CONCAT( SUBSTRING(ipb_timestamp, 1, 8), ' - ', SUBSTRING(ipb_expiry, 1, 8), ' ', ipb_expiry-ipb_timestamp )
END AS 'Length',
CASE
WHEN ipb_range_start = '' THEN 'account'
WHEN ipb_range_start = ipb_range_end THEN 'ip'
ELSE 'range'
END AS 'Type',
COUNT(*) FROM ipblocks
WHERE ipb_timestamp > 20190821000000
GROUP BY CONCAT( CASE
WHEN ipb_expiry = 'infinity' THEN 'infinity'
WHEN ipb_expiry-ipb_timestamp = 1000000 THEN '1 Day'
WHEN ipb_expiry-ipb_timestamp = 7000000 THEN '1 Week'
WHEN ipb_expiry-ipb_timestamp = 83000000 THEN '1 Week'
WHEN ipb_expiry-ipb_timestamp = 100000000 THEN '1 Month'
WHEN ipb_expiry-ipb_timestamp > 9999999999 THEN CONCAT( (ipb_expiry-ipb_timestamp) / 10000000000, ' year(s)' )
ELSE CONCAT( SUBSTRING(ipb_timestamp, 1, 8), ' - ', SUBSTRING(ipb_expiry, 1, 8), ' ', ipb_expiry-ipb_timestamp )
END,
CASE
WHEN ipb_range_start = '' THEN 'account'
WHEN ipb_range_start = ipb_range_end THEN 'ip'
ELSE 'range'
END )
By running queries you agree to the Cloud Services Terms of Use and you irrevocably agree to release your SQL under CC0 License.
All SQL code is licensed under CC0 License.