Monthly Financial Analysis

1with
2    MonthlyRevenue as (
3        select
4            date_trunc(
5                cast(
6                    format_date('%Y-%m-%d', parse_date('%d.%m.%Y', posted_date)) as date
7                )
8              , month
9            ) month
10          , sum(
11                case
12                    when amount_type = 'ItemPrice' then amount
13                    else 0
14                end
15            ) as GrossRevenue
16          , sum(
17                case
18                    when amount_type = 'Refund' then amount
19                    else 0
20                end
21            ) as Refunds
22          , avg(amount) as AverageItemPrice
23        from
24            {{raw.amazon_selling_api_oauth.settlement_report}}
25        group by
26            month
27    )
28  , OrderDiscounts as (
29        select
30            date_trunc(cast(purchase_date as date), month) month
31          , avg(item_promotion_discount) as AveragePromotionDiscount
32          , avg(shipping_price) as AverageShippingPrice
33          , avg(shipping_tax) as AverageShippingTax
34        from
35            {{staging.amazon_selling.orders_by_last_updated_date_report}}
36        group by
37            month
38    )
39select
40    r.Month
41  , r.GrossRevenue
42  , r.Refunds
43  , r.GrossRevenue - r.Refunds as NetRevenue
44  , r.AverageItemPrice
45  , d.AveragePromotionDiscount
46  , d.AverageShippingPrice
47  , d.AverageShippingTax
48from
49    MonthlyRevenue r
50    join OrderDiscounts d on r.Month = d.Month
51order by
52    r.Month desc;