0xHaM-dActive Terra Citizens - Timezones Inferred
    Updated 2023-01-17
    -- SQL credit: https://app.flipsidecrypto.com/velocity/queries/8bee2459-ae77-4769-a379-733a0b20a06d
    -- Dashboard: https://app.flipsidecrypto.com/dashboard/fQRyBU
    with timezone_label AS ( -- labels for analyses
    SELECT -12 as timezone, 16 as utc_convert, 'International Date Line West' as areas union
    SELECT -11 as timezone, 15 as utc_convert, 'Coordinated Universal Time-11' as areas union
    SELECT -10 as timezone, 14 as utc_convert, 'Hawaii' as areas union
    SELECT -9 as timezone, 13 as utc_convert, 'Alaska' as areas union
    SELECT -8 as timezone, 12 as utc_convert, 'Pacific Time - US & Canada' as areas union
    SELECT -7 as timezone, 11 as utc_convert, 'Arizona, Mountain Time - US & Canada' as areas union
    SELECT -6 as timezone, 10 as utc_convert, 'Central America, Central Time - US & Canada' as areas union
    SELECT -5 as timezone, 9 as utc_convert, 'Eastern Time - US & Canada, Indiana - East' as areas union
    SELECT -4 as timezone, 8 as utc_convert, 'Atlantic Time - Canada,' as areas union
    SELECT -3 as timezone, 7 as utc_convert, 'Brasilia, Buenos Aires, Greenland, Salvador' as areas union
    SELECT -2 as timezone, 6 as utc_convert, 'Mid-Atlantic' as areas union
    SELECT -1 as timezone, 5 as utc_convert, 'Azores, Cape Verde Is.' as areas union
    SELECT 0 as timezone, 4 as utc_convert, 'Lisbon, London' as areas union
    SELECT 1 as timezone, 3 as utc_convert, 'UK, Spain' as areas union
    SELECT 2 as timezone, 2 as utc_convert, 'Central Africa, Eastern Europe, Middle East' as areas union
    SELECT 3 as timezone, 1 as utc_convert, 'East Africa, European Russia' as areas union
    SELECT 4 as timezone, 0 as utc_convert, 'Abu Dhabi, Dubai, Port Louis' as areas union
    SELECT 5 as timezone, 23 as utc_convert, 'South & Central Asia, Mumbai, New Delhi' as areas union
    SELECT 6 as timezone, 22 as utc_convert, 'Bangladesh, Kazakhstan, Yangon' as areas union
    SELECT 7 as timezone, 21 as utc_convert, 'Bangkok, Hanoi, Jakarta, Novosibirsk' as areas union
    SELECT 8 as timezone, 20 as utc_convert, 'Southeast Asia - Singapore, Beijing, East Asia, Perth' as areas union
    SELECT 9 as timezone, 19 as utc_convert, 'East Asia - Tokyo, Seoul' as areas union
    SELECT 10 as timezone, 18 as utc_convert, 'Australia - Melbourne, Sydney, Micronesia' as areas union
    SELECT 11 as timezone, 17 as utc_convert, 'Micronesia, Melanesia' as areas union
    SELECT 12 as timezone, 16 as utc_convert, 'Auckland, Wellington, Fiji' as areas
    ),
    wallet_activity AS (
    SELECT
    TX_SENDER as address,
    count(distinct TX_ID) as tx_count,
    count(distinct extract(hour from block_timestamp)) as unique_hour_online,
    count(distinct date(block_timestamp)) as days_online,
    min(date(block_timestamp)) as first_tx,
    Run a query to Download Data