OwentellOpen Analytics Bounty: ETH vs Solana (7)
    Updated 2022-09-15
    -- Credit to Pinehearst1947 on Discord
    WITH
    solana AS (
    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_from 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
    from solana.core.fact_transfers
    Run a query to Download Data