DataScience100KnockSQL-43.sql

# S-043: レシート明細テーブル（receipt）と顧客テーブル（customer）を結合し、性別（gender）と年代（ageから計算）ごとに売上金額（amount）を合計した売上サマリテーブル（sales_summary）を作成せよ。性別は0が男性、1が女性、9が不明を表すものとする。 
# ただし、項目構成は年代、女性の売上金額、男性の売上金額、性別不明の売上金額の4項目とすること（縦に年代、横に性別のクロス集計）。また、年代は10歳ごとの階級とすること。

with recursive ls(l,u) as (
select 0,10
union all
select l+10, u+10 from ls
), levels as (
select l,u, cast(l as text) || '-' || cast(u as text) as title
from ls
limit 20
),agesummary as (
select 
c.age,
coalesce(sum(r.amount) filter (where c.gender_cd = '1'),0) as women,
coalesce(sum(r.amount) filter (where c.gender_cd = '0'),0) as men,
coalesce(sum(r.amount) filter (where c.gender_cd = '9'),0) as unknown
from receipt r
inner join customer c
on r.customer_id = c.customer_id
group by age
)
select
l.title,
sum(a.women) as women,
sum(a.men) as men,
sum(a.unknown) as unknown
from agesummary a
inner join levels l
on l.l <= a.age and a.age < l.u
group by l.title
order by l.title;