-- model: fct_revenue_v3 · gross revenue, refunds as separate line
with orders as (
select order_id, gross_amount, refund_amount, order_ts
from analytics.stg_orders
),
revenue as (
select date_trunc('month', order_ts) as month,
sum(gross_amount) as gross_revenueUsed in EB-TD-03088, Step 1
from orders group by 1
),
refunds as (
select date_trunc('month', order_ts) as month, sum(refund_amount) as refunds
from orders group by 1
)
select r.month, r.gross_revenue, f.refunds
from revenue r
left join refunds f on f.month = r.monthUsed in EB-TD-03088, Step 2