WITH quarterly_data AS (

SELECT

DATE_TRUNC('day', date) AS day,

protocol,

chain,

CASE

WHEN date >= '2024-01-01' AND date < '2024-04-01' THEN 'Q1-2024'

WHEN date >= '2024-04-01' AND date < '2024-07-01' THEN 'Q2-2024'

WHEN date >= '2024-07-01' AND date < '2024-10-01' THEN 'Q3-2024'

WHEN date >= '2024-10-01' AND date < '2025-01-01' THEN 'Q4-2024'

END AS quarter,

SUM(chain_tvl) AS daily_tvl -- Assuming 'chain_tvl' is the correct column for Total Value Locked

FROM

external.defillama.fact_protocol_tvl -- Replace with the actual table name for TVL data

WHERE

protocol ILIKE '%pangolin%'

AND chain ILIKE '%Avalanche%'

AND date BETWEEN '2024-01-01' AND '2024-12-31'

GROUP BY

day, protocol, chain, quarter -- Use 'day' instead of 'DATE_TRUNC('day', date)'

),

cumulative_quarterly_tvl AS (

SELECT

day,

protocol,

chain,

quarter,

SUM(daily_tvl) OVER (PARTITION BY protocol, quarter ORDER BY day) AS cumulative_tvl

FROM

quarterly_data

SELECT *

FROM cumulative_quarterly_tvl

ORDER BY

day, protocol;