1WITH swap_data AS (
2  SELECT
3    CAST(time AS DATE) AS activity_date,
4    COUNT(*) AS swap_count,
5    SUM(inputamount + outputamount) AS swap_volume
6  FROM @portals.transactions.swaps
7  WHERE inputtoken LIKE 'ethereum:%'
8    AND outputtoken LIKE 'ethereum:%'
9  GROUP BY activity_date
10),
11
12deposit_data AS (
13  SELECT
14    CAST(time AS DATE) AS activity_date,
15    COUNT(*) AS deposit_count,
16    SUM(amount) AS deposit_volume
17  FROM @portals.transactions.deposits
18  WHERE key LIKE 'ethereum:%'
19  GROUP BY activity_date
20),
21
22withdraw_data AS (
23  SELECT
24    CAST(time AS DATE) AS activity_date,
25    COUNT(*) AS withdrawal_count,
26    SUM(amount) AS withdrawal_volume
27  FROM @portals.transactions.withdraws
28  WHERE key LIKE 'ethereum:%'
29  GROUP BY activity_date
30)
31
32SELECT
33  COALESCE(s.activity_date, d.activity_date, w.activity_date) AS date,
34  s.swap_count,
35  s.swap_volume,
36  d.deposit_count,
37  d.deposit_volume,
38  w.withdrawal_count,
39  w.withdrawal_volume
40FROM swap_data s
41FULL OUTER JOIN deposit_data d ON s.activity_date = d.activity_date
42FULL OUTER JOIN withdraw_data w ON COALESCE(s.activity_date, d.activity_date) = w.activity_date
43ORDER BY date;