Query editor
SELECT date_trunc('week', time) as time-- , symbol_name-- , amount_value-- , user-- , SUM(CASE WHEN tx_type = 'Deposit' THEN amount_value END) as deposit_value-- , SUM(CASE WHEN tx_type = 'Withdraw' THEN amount_value END) as withdraw_value-- , SUM(CASE WHEN tx_type = 'Withdraw' THEN -amount_value END) as withdraw_negative_value, SUM(CASE WHEN tx_type = 'Borrow' THEN amount_value END) as borrow_value, SUM(CASE WHEN tx_type = 'Repay' THEN amount_value END) as repay_value-- , SUM(CASE WHEN tx_type = 'Buy' THEN amount_value END) as buy_value, SUM(CASE WHEN tx_type = 'Liquidate' THEN amount_value END) as liquidate_value-- , SUM(CASE WHEN tx_type = 'Swap' THEN amount_value END) as swap_value-- , SUM(CASE WHEN tx_type = 'Trade' THEN amount_value END) as trade_value, SUM(CASE WHEN tx_type IN ('Deposit', 'Borrow', 'Repay') THEN amount_value END) as money_market_value, SUM(amount_value) as total_value, COUNT(DISTINCT CASE WHEN tx_type = 'Deposit' THEN user END) as deposit_users-- , COUNT(DISTINCT CASE WHEN tx_type = 'Withdraw' THEN user END) as withdraw_users, COUNT(DISTINCT CASE WHEN tx_type = 'Borrow' THEN user END) as borrow_users, COUNT(DISTINCT CASE WHEN tx_type = 'Repay' THEN user END) as repay_users, COUNT(DISTINCT CASE WHEN tx_type = 'Liquidate' Then user END) as liquidate_users-- , COUNT(DISTINCT CASE WHEN tx_type = 'Buy' THEN user END) as buy_users-- , COUNT(DISTINCT CASE WHEN tx_type = 'Sell' THEN user END) as sell_users-- , COUNT(DISTINCT CASE WHEN tx_type = 'Trade' THEN user END) as trade_users-- , COUNT(DISTINCT CASE WHEN tx_type = 'Swap' THEN user END) as swap_users-- , COUNT(distinct user) as unique_users, SUM(SUM(CASE WHEN tx_type IN ('Liquidate') THEN amount_value END)) OVER (ORDER BY date_trunc('week', time))/1e6 as total_liquidate_format-- , SUM(SUM(CASE WHEN tx_type IN ('Swap', 'Buy') THEN amount_value END)) OVER (ORDER BY date_trunc('week', time))/1e6 as total_swap_format, SUM(SUM(CASE WHEN tx_type IN ('Deposit') THEN amount_value END)) OVER (ORDER BY date_trunc('week', time))/1e9 as total_deposit_format-- , SUM(SUM(CASE WHEN tx_type IN ('Withdraw') THEN amount_value END)) OVER (ORDER BY date_trunc('week', time))/1e9 as total_withdraw_format-- , SUM(SUM(CASE WHEN tx_type IN ('Deposit') THEN amount_value WHEN tx_type = 'Withdraw' THEN -amount_value END)) OVER (ORDER BY date_trunc('week', time))/1e6 as total_netflow_format-- , SUM(SUM(CASE WHEN tx_type IN ('Deposit') THEN amount_value WHEN tx_type = 'Withdraw' THEN -amount_value END)) OVER (ORDER BY date_trunc('week', time)) as total_netflow, SUM(SUM(CASE WHEN tx_type IN ('Deposit', 'Borrow', 'Repay') THEN amount_value END)) OVER (ORDER BY date_trunc('week', time)) as cumulative_money_market_volume-- , SUM(SUM(CASE WHEN tx_type IN ('Deposit', 'Trade', 'Swap', 'Borrow', 'Repay') THEN amount_value END)) OVER (ORDER BY date_trunc('week', time)) as cumulative_volume-- , SUM(SUM(CASE WHEN tx_type IN ('Deposit', 'Trade', 'Swap', 'Borrow', 'Repay') THEN amount_value END)) OVER (ORDER BY date_trunc('week', time))/1e9 as cumulative_volume_format, SUM(SUM(CASE WHEN tx_type IN ('Borrow') THEN amount_value END)) OVER (ORDER BY date_trunc('week', time))/1e9 as cumulative_borrow_format, SUM(SUM(CASE WHEN tx_type IN ('Repay') THEN amount_value END)) OVER (ORDER BY date_trunc('week', time))/1e9 as cumulative_repay_format-- , SUM(SUM(CASE WHEN tx_type IN ('Deposit', 'Borrow', 'Repay') THEN amount_value END)) OVER (ORDER BY date_trunc('week', time))/1e6 as cumulative_volume_format-- , SUM(SUM(CASE WHEN tx_type IN ('Swap', 'Borrow', 'Repay') THEN amount_value END)) OVER (ORDER BY date_trunc('week', time))/1e6 as cumulative_volume_format-- , SUM(SUM(CASE WHEN rank_num = 1 THEN 1 ELSE 0 END)) OVER (ORDER BY date_trunc('week', time)) as cumulative_unique_users-- , SUM(SUM(CASE WHEN tx_type IN ('Deposit') AND time >= now() - interval '2' day then amount_value ELSE 0 END)) OVER (ORDER BY date_trunc('week', time))/1e6 as daily_lent_volume, SUM(SUM(CASE WHEN tx_type IN ('Borrow') AND time >= now() - interval '2' day then amount_value ELSE 0 END)) OVER (ORDER BY date_trunc('week', time))/1e6 as daily_borrow_volume, SUM(SUM(CASE WHEN tx_type IN ('Repay') AND time >= now() - interval '2' day then amount_value ELSE 0 END)) OVER (ORDER BY date_trunc('week', time))/1e6 as daily_repay_volume, SUM(SUM(CASE WHEN tx_type IN ('Deposit') AND time >= now() - interval '7' day then amount_value ELSE 0 END)) OVER (ORDER BY date_trunc('week', time))/1e6 as weekly_lent_volume, SUM(SUM(CASE WHEN tx_type IN ('Borrow') AND time >= now() - interval '7' day then amount_value ELSE 0 END)) OVER (ORDER BY date_trunc('week', time))/1e6 as weekly_borrow_volume, SUM(SUM(CASE WHEN tx_type IN ('Repay') AND time >= now() - interval '7' day then amount_value ELSE 0 END)) OVER (ORDER BY date_trunc('week', time))/1e6 as weekly_repay_volume, SUM(SUM(CASE WHEN tx_type IN ('Deposit') AND time >= now() - interval '1' month then amount_value ELSE 0 END)) OVER (ORDER BY date_trunc('week', time))/1e6 as monthly_lent_volume, SUM(SUM(CASE WHEN tx_type IN ('Borrow') AND time >= now() - interval '1' month then amount_value ELSE 0 END)) OVER (ORDER BY date_trunc('week', time))/1e6 as monthly_borrow_volume, SUM(SUM(CASE WHEN tx_type IN ('Repay') AND time >= now() - interval '1' month then amount_value ELSE 0 END)) OVER (ORDER BY date_trunc('week', time))/1e6 as monthly_repay_volume-- , SUM(SUM(CASE WHEN daily_rank_num = 1 AND time >= now() - interval '2' day then 1 ELSE 0 END)) OVER (ORDER BY date_trunc('week', time)) as daily_unique_users-- , SUM(SUM(CASE WHEN weekly_rank_num = 1 AND time >= now() - interval '7' day THEN 1 ELSE 0 END)) OVER (ORDER BY date_trunc('week', time)) as weekly_unique_users-- , SUM(SUM(CASE WHEN monthly_rank_num = 1 AND time >= now() - interval '1' month THEN 1 ELSE 0 END)) OVER (ORDER BY date_trunc('week', time)) as monthly_unique_usersFROM query_4831271GROUP BY 1ORDER BY 1 DESC