with base AS (

date_trunc('day', block_timestamp) as date,

label_type as sector,

count(DISTINCT from_address) as users,

count(DISTINCT tx_hash) as transactions,

sum(tx_fee) as "Fee ($Klay)"

kaia.core.fact_transactions s

LEFT JOIN kaia.core.dim_labels a on s.to_address = a.address

WHERE

block_timestamp :: date >= '{{Start_Date}}'

AND block_timestamp :: date <= '{{End_Date}}'

AND block_timestamp :: date <= current_date - 1

AND label_type NOT IN ('token', 'chadmin')

GROUP BY

1,

2

HAVING

sector IS NOT NULL

SELECT

SUM(CASE WHEN date >= current_date - interval '1 Day' then users end) as active_users_24,

SUM(CASE WHEN date >= current_date - interval '7 Day' then users end) as active_users_7d,

SUM(CASE WHEN date >= current_date - interval '30 Day' then users end) as active_users_30

from base