OwentellOpen Analytics Bounty: ETH vs Solana (7)
Updated 2022-09-15
99
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
›
⌄
-- 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