Nested Subtotals

VSCode

Nested subtotals are quite painful to do in SQL, requiring either self-joins, window functions, or GROUP BY ROLLUP queries. Unfortunately for analysts, it's also a very common type of analysis requested by business owners; for example, determing which segments of a population drove revenue growth, or drilling down from annual numbers into more granular time periods, like quarters or months.

To see how we do this in Malloy, let's look at the following simple model:

source: order_items is duckdb.table('../data/order_items.parquet') extend {
  primary_key: id

  measure:
    total_sales is sale_price.sum()
}

This contains a single table of order items, and a measure to calculate sales. We can use this to calculate sales by year:

run: order_items -> {
  group_by: fiscal_year is created_at.year
  aggregate: total_sales
  order_by: fiscal_year desc
}

QUERY RESULTS

fiscal_year	total_sales
2022	4,744,743.447
2021	3,900,910.814
2020	2,602,043.069
2019	1,318,595.545

[
  {
    "fiscal_year": "2022-01-01T00:00:00.000Z",
    "total_sales": 4744743.446900487
  },
  {
    "fiscal_year": "2021-01-01T00:00:00.000Z",
    "total_sales": 3900910.8143820763
  },
  {
    "fiscal_year": "2020-01-01T00:00:00.000Z",
    "total_sales": 2602043.069360018
  },
  {
    "fiscal_year": "2019-01-01T00:00:00.000Z",
    "total_sales": 1318595.5448319912
  }
]

SELECT 
   DATE_TRUNC('year', order_items."created_at") as "fiscal_year",
   COALESCE(SUM(order_items."sale_price"),0) as "total_sales"
FROM '../data/order_items.parquet' as order_items
GROUP BY 1
ORDER BY 1 desc NULLS LAST

Now suppose we want to drill into the sales numbers by quarter. To do this in SQL would require either a window function, a self-join, or a GROUP BY ROLLUP, all of which are complicated to implement, and produce results that are difficult to interpret. In Malloy, none of this is necessary, we simply use a nest clause:

run: order_items -> {
  group_by: fiscal_year is created_at.year
  aggregate: total_sales
  order_by: fiscal_year desc

  nest: by_quarter is {
    group_by: fiscal_quarter is created_at.quarter
    aggregate: total_sales
    order_by: fiscal_quarter 
  }
}

QUERY RESULTS

fiscal_year

total_sales

by_quarter

2022

4,744,743.447

fiscal_quarter	total_sales
2022-Q1	1,222,323.154
2022-Q2	1,302,098.964
2022-Q3	1,454,325.496
2022-Q4	765,995.833

2021

3,900,910.814

fiscal_quarter	total_sales
2021-Q1	848,129.963
2021-Q2	877,377.033
2021-Q3	1,010,409.544
2021-Q4	1,164,994.274

2020

2,602,043.069

fiscal_quarter	total_sales
2020-Q1	531,607.742
2020-Q2	578,336.472
2020-Q3	671,472.152
2020-Q4	820,626.703

2019

1,318,595.545

fiscal_quarter	total_sales
2019-Q1	187,058.771
2019-Q2	277,457.261
2019-Q3	364,416.351
2019-Q4	489,663.162

[
  {
    "fiscal_year": "2022-01-01T00:00:00.000Z",
    "total_sales": 4744743.446900487,
    "by_quarter": [
      {
        "fiscal_quarter": "2022-01-01T00:00:00.000Z",
        "total_sales": 1222323.153985262
      },
      {
        "fiscal_quarter": "2022-04-01T00:00:00.000Z",
        "total_sales": 1302098.9644565582
      },
      {
        "fiscal_quarter": "2022-07-01T00:00:00.000Z",
        "total_sales": 1454325.4955222607
      },
      {
        "fiscal_quarter": "2022-10-01T00:00:00.000Z",
        "total_sales": 765995.8329364061
      }
    ]
  },
  {
    "fiscal_year": "2021-01-01T00:00:00.000Z",
    "total_sales": 3900910.8143820763,
    "by_quarter": [
      {
        "fiscal_quarter": "2021-01-01T00:00:00.000Z",
        "total_sales": 848129.9632571936
      },
      {
        "fiscal_quarter": "2021-04-01T00:00:00.000Z",
        "total_sales": 877377.0331277847
      },
      {
        "fiscal_quarter": "2021-07-01T00:00:00.000Z",
        "total_sales": 1010409.5437389612
      },
      {
        "fiscal_quarter": "2021-10-01T00:00:00.000Z",
        "total_sales": 1164994.2742581367
      }
    ]
  },
  {
    "fiscal_year": "2020-01-01T00:00:00.000Z",
    "total_sales": 2602043.069360018,
    "by_quarter": [
      {
        "fiscal_quarter": "2020-01-01T00:00:00.000Z",
        "total_sales": 531607.7420347929
      },
      {
        "fiscal_quarter": "2020-04-01T00:00:00.000Z",
        "total_sales": 578336.4721107483
      },
      {
        "fiscal_quarter": "2020-07-01T00:00:00.000Z",
        "total_sales": 671472.1522718668
      },
      {
        "fiscal_quarter": "2020-10-01T00:00:00.000Z",
        "total_sales": 820626.7029426098
      }
    ]
  },
  {
    "fiscal_year": "2019-01-01T00:00:00.000Z",
    "total_sales": 1318595.5448319912,
    "by_quarter": [
      {
        "fiscal_quarter": "2019-01-01T00:00:00.000Z",
        "total_sales": 187058.77078318596
      },
      {
        "fiscal_quarter": "2019-04-01T00:00:00.000Z",
        "total_sales": 277457.2608318329
      },
      {
        "fiscal_quarter": "2019-07-01T00:00:00.000Z",
        "total_sales": 364416.3513685465
      },
      {
        "fiscal_quarter": "2019-10-01T00:00:00.000Z",
        "total_sales": 489663.16184842587
      }
    ]
  }
]

WITH __stage0 AS (
  SELECT
    group_set,
    DATE_TRUNC('year', order_items."created_at") as "fiscal_year__0",
    CASE WHEN group_set=0 THEN
      COALESCE(SUM(order_items."sale_price"),0)
      END as "total_sales__0",
    CASE WHEN group_set=1 THEN
      DATE_TRUNC('quarter', order_items."created_at")
      END as "fiscal_quarter__1",
    CASE WHEN group_set=1 THEN
      COALESCE(SUM(order_items."sale_price"),0)
      END as "total_sales__1"
  FROM '../data/order_items.parquet' as order_items
  CROSS JOIN (SELECT UNNEST(GENERATE_SERIES(0,1,1)) as group_set  ) as group_set
  GROUP BY 1,2,4
)
SELECT
  "fiscal_year__0" as "fiscal_year",
  MAX(CASE WHEN group_set=0 THEN "total_sales__0" END) as "total_sales",
  COALESCE(LIST({
    "fiscal_quarter": "fiscal_quarter__1", 
    "total_sales": "total_sales__1"}  ORDER BY  "fiscal_quarter__1" ASC NULLS LAST) FILTER (WHERE group_set=1),[]) as "by_quarter"
FROM __stage0
GROUP BY 1
ORDER BY 1 desc NULLS LAST

To drill down even further, it's trivial to repeat this pattern once again. The following query looks at the top 5 sales days for each fiscal quarter

run: order_items -> {
  group_by: fiscal_year is created_at.year
  aggregate: total_sales
  order_by: fiscal_year desc

  nest: by_quarter is  {
    group_by: fiscal_quarter is created_at.quarter
    aggregate: total_sales
    order_by: fiscal_quarter 

    nest: top_days is  {
      group_by: sale_date is created_at.day
      aggregate: total_sales
      limit: 5
    }
  }
}

QUERY RESULTS

fiscal_year

total_sales

by_quarter

2022

4,744,743.447

fiscal_quarter

total_sales

top_days

2022-Q1

1,222,323.154

sale_date	total_sales
2022-03-31	15,838
2022-03-30	15,060.81
2022-03-29	14,276.18
2022-03-28	16,135.75
2022-03-27	9,974.51

2022-Q2

1,302,098.964

sale_date	total_sales
2022-06-30	14,788.7
2022-06-29	15,081.44
2022-06-28	15,985.9
2022-06-27	17,816.17
2022-06-26	10,816.15

2022-Q3

1,454,325.496

sale_date	total_sales
2022-09-30	18,377.75
2022-09-29	18,631.13
2022-09-28	16,604.31
2022-09-27	21,601.17
2022-09-26	21,291.52

2022-Q4

765,995.833

sale_date	total_sales
2022-11-14	11,470.22
2022-11-13	12,687.75
2022-11-12	14,292.66
2022-11-11	16,805.99
2022-11-10	19,224.2

2021

3,900,910.814

fiscal_quarter

total_sales

top_days

2021-Q1

848,129.963

sale_date	total_sales
2021-03-31	8,207.75
2021-03-30	10,024.95
2021-03-29	11,538.27
2021-03-28	7,214.58
2021-03-27	6,354.59

2021-Q2

877,377.033

sale_date	total_sales
2021-06-30	9,473.95
2021-06-29	10,071.45
2021-06-28	12,266.55
2021-06-27	9,190.42
2021-06-26	8,406.03

2021-Q3

1,010,409.544

sale_date	total_sales
2021-09-30	15,300.07
2021-09-29	11,604.31
2021-09-28	16,057.42
2021-09-27	13,798.09
2021-09-26	10,735.4

2021-Q4

1,164,994.274

sale_date	total_sales
2021-12-31	8,400.81
2021-12-30	14,864.99
2021-12-29	12,166.21
2021-12-28	17,893.94
2021-12-27	16,780.93

2020

2,602,043.069

fiscal_quarter

total_sales

top_days

2020-Q1

531,607.742

sale_date	total_sales
2020-03-31	8,762.63
2020-03-30	6,353.27
2020-03-29	4,788.21
2020-03-28	5,371.49
2020-03-27	6,531.56

2020-Q2

578,336.472

sale_date	total_sales
2020-06-30	7,549.3
2020-06-29	7,506.21
2020-06-28	5,140.56
2020-06-27	4,646.84
2020-06-26	6,472.6

2020-Q3

671,472.152

sale_date	total_sales
2020-09-30	8,241.39
2020-09-29	9,371.4
2020-09-28	9,755.48
2020-09-27	5,392.87
2020-09-26	5,581.39

2020-Q4

820,626.703

sale_date	total_sales
2020-12-31	5,959.61
2020-12-30	12,423.39
2020-12-29	12,249.6
2020-12-28	12,106.75
2020-12-27	7,321.61

2019

1,318,595.545

fiscal_quarter

total_sales

top_days

2019-Q1

187,058.771

sale_date	total_sales
2019-03-31	1,572.8
2019-03-30	1,326.03
2019-03-29	1,853.89
2019-03-28	2,834.88
2019-03-27	2,447.68

2019-Q2

277,457.261

sale_date	total_sales
2019-06-30	1,712.37
2019-06-29	2,904.37
2019-06-28	2,507.9
2019-06-27	3,338.96
2019-06-26	4,174.5

2019-Q3

364,416.351

sale_date	total_sales
2019-09-30	5,101.67
2019-09-29	4,670.12
2019-09-28	3,791.18
2019-09-27	4,934.63
2019-09-26	6,435.99

2019-Q4

489,663.162

sale_date	total_sales
2019-12-31	2,834.77
2019-12-30	7,551.17
2019-12-29	3,886.42
2019-12-28	4,340.54
2019-12-27	6,892.75

[
  {
    "fiscal_year": "2022-01-01T00:00:00.000Z",
    "total_sales": 4744743.446900487,
    "by_quarter": [
      {
        "fiscal_quarter": "2022-01-01T00:00:00.000Z",
        "total_sales": 1222323.153985262,
        "top_days": [
          {
            "sale_date": "2022-03-31T00:00:00.000Z",
            "total_sales": 15838.000048160553
          },
          {
            "sale_date": "2022-03-30T00:00:00.000Z",
            "total_sales": 15060.81005692482
          },
          {
            "sale_date": "2022-03-29T00:00:00.000Z",
            "total_sales": 14276.180056810379
          },
          {
            "sale_date": "2022-03-28T00:00:00.000Z",
            "total_sales": 16135.75002336502
          },
          {
            "sale_date": "2022-03-27T00:00:00.000Z",
            "total_sales": 9974.510008335114
          }
        ]
      },
      {
        "fiscal_quarter": "2022-04-01T00:00:00.000Z",
        "total_sales": 1302098.9644565582,
        "top_days": [
          {
            "sale_date": "2022-06-30T00:00:00.000Z",
            "total_sales": 14788.70004415512
          },
          {
            "sale_date": "2022-06-29T00:00:00.000Z",
            "total_sales": 15081.440027952194
          },
          {
            "sale_date": "2022-06-28T00:00:00.000Z",
            "total_sales": 15985.900051116943
          },
          {
            "sale_date": "2022-06-27T00:00:00.000Z",
            "total_sales": 17816.170053720474
          },
          {
            "sale_date": "2022-06-26T00:00:00.000Z",
            "total_sales": 10816.15003323555
          }
        ]
      },
      {
        "fiscal_quarter": "2022-07-01T00:00:00.000Z",
        "total_sales": 1454325.4955222607,
        "top_days": [
          {
            "sale_date": "2022-09-30T00:00:00.000Z",
            "total_sales": 18377.750062704086
          },
          {
            "sale_date": "2022-09-29T00:00:00.000Z",
            "total_sales": 18631.130081653595
          },
          {
            "sale_date": "2022-09-28T00:00:00.000Z",
            "total_sales": 16604.310045957565
          },
          {
            "sale_date": "2022-09-27T00:00:00.000Z",
            "total_sales": 21601.170073747635
          },
          {
            "sale_date": "2022-09-26T00:00:00.000Z",
            "total_sales": 21291.520059108734
          }
        ]
      },
      {
        "fiscal_quarter": "2022-10-01T00:00:00.000Z",
        "total_sales": 765995.8329364061,
        "top_days": [
          {
            "sale_date": "2022-11-14T00:00:00.000Z",
            "total_sales": 11470.220048904419
          },
          {
            "sale_date": "2022-11-13T00:00:00.000Z",
            "total_sales": 12687.750051021576
          },
          {
            "sale_date": "2022-11-12T00:00:00.000Z",
            "total_sales": 14292.660077095032
          },
          {
            "sale_date": "2022-11-11T00:00:00.000Z",
            "total_sales": 16805.99007320404
          },
          {
            "sale_date": "2022-11-10T00:00:00.000Z",
            "total_sales": 19224.200048446655
          }
        ]
      }
    ]
  },
  {
    "fiscal_year": "2021-01-01T00:00:00.000Z",
    "total_sales": 3900910.8143820763,
    "by_quarter": [
      {
        "fiscal_quarter": "2021-01-01T00:00:00.000Z",
        "total_sales": 848129.9632571936,
        "top_days": [
          {
            "sale_date": "2021-03-31T00:00:00.000Z",
            "total_sales": 8207.750026464462
          },
          {
            "sale_date": "2021-03-30T00:00:00.000Z",
            "total_sales": 10024.950029373169
          },
          {
            "sale_date": "2021-03-29T00:00:00.000Z",
            "total_sales": 11538.270048618317
          },
          {
            "sale_date": "2021-03-28T00:00:00.000Z",
            "total_sales": 7214.580015301704
          },
          {
            "sale_date": "2021-03-27T00:00:00.000Z",
            "total_sales": 6354.590037822723
          }
        ]
      },
      {
        "fiscal_quarter": "2021-04-01T00:00:00.000Z",
        "total_sales": 877377.0331277847,
        "top_days": [
          {
            "sale_date": "2021-06-30T00:00:00.000Z",
            "total_sales": 9473.950018882751
          },
          {
            "sale_date": "2021-06-29T00:00:00.000Z",
            "total_sales": 10071.450026988983
          },
          {
            "sale_date": "2021-06-28T00:00:00.000Z",
            "total_sales": 12266.550037384033
          },
          {
            "sale_date": "2021-06-27T00:00:00.000Z",
            "total_sales": 9190.42005109787
          },
          {
            "sale_date": "2021-06-26T00:00:00.000Z",
            "total_sales": 8406.03002500534
          }
        ]
      },
      {
        "fiscal_quarter": "2021-07-01T00:00:00.000Z",
        "total_sales": 1010409.5437389612,
        "top_days": [
          {
            "sale_date": "2021-09-30T00:00:00.000Z",
            "total_sales": 15300.07003736496
          },
          {
            "sale_date": "2021-09-29T00:00:00.000Z",
            "total_sales": 11604.310046195984
          },
          {
            "sale_date": "2021-09-28T00:00:00.000Z",
            "total_sales": 16057.420017004013
          },
          {
            "sale_date": "2021-09-27T00:00:00.000Z",
            "total_sales": 13798.09006011486
          },
          {
            "sale_date": "2021-09-26T00:00:00.000Z",
            "total_sales": 10735.400017261505
          }
        ]
      },
      {
        "fiscal_quarter": "2021-10-01T00:00:00.000Z",
        "total_sales": 1164994.2742581367,
        "top_days": [
          {
            "sale_date": "2021-12-31T00:00:00.000Z",
            "total_sales": 8400.810025930405
          },
          {
            "sale_date": "2021-12-30T00:00:00.000Z",
            "total_sales": 14864.990024089813
          },
          {
            "sale_date": "2021-12-29T00:00:00.000Z",
            "total_sales": 12166.21004152298
          },
          {
            "sale_date": "2021-12-28T00:00:00.000Z",
            "total_sales": 17893.940051555634
          },
          {
            "sale_date": "2021-12-27T00:00:00.000Z",
            "total_sales": 16780.93005490303
          }
        ]
      }
    ]
  },
  {
    "fiscal_year": "2020-01-01T00:00:00.000Z",
    "total_sales": 2602043.069360018,
    "by_quarter": [
      {
        "fiscal_quarter": "2020-01-01T00:00:00.000Z",
        "total_sales": 531607.7420347929,
        "top_days": [
          {
            "sale_date": "2020-03-31T00:00:00.000Z",
            "total_sales": 8762.63003373146
          },
          {
            "sale_date": "2020-03-30T00:00:00.000Z",
            "total_sales": 6353.270024061203
          },
          {
            "sale_date": "2020-03-29T00:00:00.000Z",
            "total_sales": 4788.210015296936
          },
          {
            "sale_date": "2020-03-28T00:00:00.000Z",
            "total_sales": 5371.490037918091
          },
          {
            "sale_date": "2020-03-27T00:00:00.000Z",
            "total_sales": 6531.560037612915
          }
        ]
      },
      {
        "fiscal_quarter": "2020-04-01T00:00:00.000Z",
        "total_sales": 578336.4721107483,
        "top_days": [
          {
            "sale_date": "2020-06-30T00:00:00.000Z",
            "total_sales": 7549.300024032593
          },
          {
            "sale_date": "2020-06-29T00:00:00.000Z",
            "total_sales": 7506.210007190704
          },
          {
            "sale_date": "2020-06-28T00:00:00.000Z",
            "total_sales": 5140.560017585754
          },
          {
            "sale_date": "2020-06-27T00:00:00.000Z",
            "total_sales": 4646.8400230407715
          },
          {
            "sale_date": "2020-06-26T00:00:00.000Z",
            "total_sales": 6472.60002207756
          }
        ]
      },
      {
        "fiscal_quarter": "2020-07-01T00:00:00.000Z",
        "total_sales": 671472.1522718668,
        "top_days": [
          {
            "sale_date": "2020-09-30T00:00:00.000Z",
            "total_sales": 8241.390033960342
          },
          {
            "sale_date": "2020-09-29T00:00:00.000Z",
            "total_sales": 9371.40002655983
          },
          {
            "sale_date": "2020-09-28T00:00:00.000Z",
            "total_sales": 9755.480032444
          },
          {
            "sale_date": "2020-09-27T00:00:00.000Z",
            "total_sales": 5392.870006084442
          },
          {
            "sale_date": "2020-09-26T00:00:00.000Z",
            "total_sales": 5581.3900146484375
          }
        ]
      },
      {
        "fiscal_quarter": "2020-10-01T00:00:00.000Z",
        "total_sales": 820626.7029426098,
        "top_days": [
          {
            "sale_date": "2020-12-31T00:00:00.000Z",
            "total_sales": 5959.610029220581
          },
          {
            "sale_date": "2020-12-30T00:00:00.000Z",
            "total_sales": 12423.390022277832
          },
          {
            "sale_date": "2020-12-29T00:00:00.000Z",
            "total_sales": 12249.600047111511
          },
          {
            "sale_date": "2020-12-28T00:00:00.000Z",
            "total_sales": 12106.750036001205
          },
          {
            "sale_date": "2020-12-27T00:00:00.000Z",
            "total_sales": 7321.610027313232
          }
        ]
      }
    ]
  },
  {
    "fiscal_year": "2019-01-01T00:00:00.000Z",
    "total_sales": 1318595.5448319912,
    "by_quarter": [
      {
        "fiscal_quarter": "2019-01-01T00:00:00.000Z",
        "total_sales": 187058.77078318596,
        "top_days": [
          {
            "sale_date": "2019-03-31T00:00:00.000Z",
            "total_sales": 1572.800009727478
          },
          {
            "sale_date": "2019-03-30T00:00:00.000Z",
            "total_sales": 1326.0300011634827
          },
          {
            "sale_date": "2019-03-29T00:00:00.000Z",
            "total_sales": 1853.890013217926
          },
          {
            "sale_date": "2019-03-28T00:00:00.000Z",
            "total_sales": 2834.880018234253
          },
          {
            "sale_date": "2019-03-27T00:00:00.000Z",
            "total_sales": 2447.680019378662
          }
        ]
      },
      {
        "fiscal_quarter": "2019-04-01T00:00:00.000Z",
        "total_sales": 277457.2608318329,
        "top_days": [
          {
            "sale_date": "2019-06-30T00:00:00.000Z",
            "total_sales": 1712.3700094223022
          },
          {
            "sale_date": "2019-06-29T00:00:00.000Z",
            "total_sales": 2904.3700094223022
          },
          {
            "sale_date": "2019-06-28T00:00:00.000Z",
            "total_sales": 2507.899995326996
          },
          {
            "sale_date": "2019-06-27T00:00:00.000Z",
            "total_sales": 3338.9600014686584
          },
          {
            "sale_date": "2019-06-26T00:00:00.000Z",
            "total_sales": 4174.500011444092
          }
        ]
      },
      {
        "fiscal_quarter": "2019-07-01T00:00:00.000Z",
        "total_sales": 364416.3513685465,
        "top_days": [
          {
            "sale_date": "2019-09-30T00:00:00.000Z",
            "total_sales": 5101.670015335083
          },
          {
            "sale_date": "2019-09-29T00:00:00.000Z",
            "total_sales": 4670.120021343231
          },
          {
            "sale_date": "2019-09-28T00:00:00.000Z",
            "total_sales": 3791.18000125885
          },
          {
            "sale_date": "2019-09-27T00:00:00.000Z",
            "total_sales": 4934.630023479462
          },
          {
            "sale_date": "2019-09-26T00:00:00.000Z",
            "total_sales": 6435.990019798279
          }
        ]
      },
      {
        "fiscal_quarter": "2019-10-01T00:00:00.000Z",
        "total_sales": 489663.16184842587,
        "top_days": [
          {
            "sale_date": "2019-12-31T00:00:00.000Z",
            "total_sales": 2834.770021915436
          },
          {
            "sale_date": "2019-12-30T00:00:00.000Z",
            "total_sales": 7551.170014858246
          },
          {
            "sale_date": "2019-12-29T00:00:00.000Z",
            "total_sales": 3886.420011997223
          },
          {
            "sale_date": "2019-12-28T00:00:00.000Z",
            "total_sales": 4340.540011405945
          },
          {
            "sale_date": "2019-12-27T00:00:00.000Z",
            "total_sales": 6892.750037193298
          }
        ]
      }
    ]
  }
]

WITH __stage0 AS (
  SELECT
    group_set,
    DATE_TRUNC('year', order_items."created_at") as "fiscal_year__0",
    CASE WHEN group_set=0 THEN
      COALESCE(SUM(order_items."sale_price"),0)
      END as "total_sales__0",
    CASE WHEN group_set IN (1,2) THEN
      DATE_TRUNC('quarter', order_items."created_at")
      END as "fiscal_quarter__1",
    CASE WHEN group_set=1 THEN
      COALESCE(SUM(order_items."sale_price"),0)
      END as "total_sales__1",
    CASE WHEN group_set=2 THEN
      DATE_TRUNC('day', order_items."created_at")
      END as "sale_date__2",
    CASE WHEN group_set=2 THEN
      COALESCE(SUM(order_items."sale_price"),0)
      END as "total_sales__2"
  FROM '../data/order_items.parquet' as order_items
  CROSS JOIN (SELECT UNNEST(GENERATE_SERIES(0,2,1)) as group_set  ) as group_set
  GROUP BY 1,2,4,6
)
, __stage1 AS (
  SELECT 
    CASE WHEN group_set=2 THEN 1  ELSE group_set END as group_set,
    "fiscal_year__0" as "fiscal_year__0",
    FIRST("total_sales__0") FILTER (WHERE "total_sales__0" IS NOT NULL) as "total_sales__0",
    CASE WHEN group_set IN (1,2) THEN
      "fiscal_quarter__1"
      END as "fiscal_quarter__1",
    FIRST("total_sales__1") FILTER (WHERE "total_sales__1" IS NOT NULL) as "total_sales__1",
    COALESCE(LIST({
      "sale_date": "sale_date__2", 
      "total_sales": "total_sales__2"}  ORDER BY  "sale_date__2" desc NULLS LAST) FILTER (WHERE group_set=2)[1:5],[]) as "top_days__1"
  FROM __stage0
  GROUP BY 1,2,4
)
SELECT
  "fiscal_year__0" as "fiscal_year",
  MAX(CASE WHEN group_set=0 THEN "total_sales__0" END) as "total_sales",
  COALESCE(LIST({
    "fiscal_quarter": "fiscal_quarter__1", 
    "total_sales": "total_sales__1", 
    "top_days": "top_days__1"}  ORDER BY  "fiscal_quarter__1" ASC NULLS LAST) FILTER (WHERE group_set=1),[]) as "by_quarter"
FROM __stage1
GROUP BY 1
ORDER BY 1 desc NULLS LAST

These queries are trivial to implement, and easy to understand. If you'd like a challenge, try implementing the same thing in SQL and see what it looks like.