SQLコードをひたすらに書きまくる部

SQLコード書きまくる部です。経験を積みたい。
今回は、SQL道場というサイトでコードを書いていきます。

基本事項をまとめるの会

知ってる基本事項をまとめます　コピペ最高！

処理順序

SQL文の処理順序は以下の通りです。今回は、

step	文	内容
1	from	itemsテーブルから
2	where	item_priceが300以上の行を
3	group by	item_shopでグルーピングして
4	having	平均価格が300以上の行を表示する
5	select	item_shopと店舗ごとの平均価格を選び
6	order by	平均価格を降順で並び変えて
7	limit	表示件数を20に制限する

という順序で処理をしています。(適当に作ったサンプル)コードは以下の通りです。

sample

SELECT -- 表示する列の選択
    item_shop, -- 前カンマ、後ろカンマどちらでもOK
    AVG(item_price) AS "avg_price"
    -- group byで分けたグループごとに集計関数で集計している
    -- また、平均価格の列を"avg_price"と命名
FROM
    items 
    -- データを引っ張るテーブルを指定
WHERE
    item_price > 300 
    -- テーブル全体から行単位で条件付け
GROUP BY
    item_shop 
    -- グループ化するカラムを選択
    -- ちなみに、group byに入ったカラム名以外は、select上では集計関数を走らせないとエラー
HAVING
    AVG(item_price) > 300 
    -- where文で処理した後にさらに条件付け
ORDER BY
    avg_price DESC
    -- 降順で並び替え(昇順ならascか無記載)
LIMIT
    20
    -- 表示件数の制限
; -- コードの終了宣言？(入れないとエラー)

以下、注意事項(兼備忘録です)

select句の処理順序

ここで注意したいのは、select句の処理順序は、記述順序とは異なりだいぶ後ろの方だということです。なので、例えば今回平均価格を"avg_price"と命名していますが、この名前を指定して機能するのはHAVING句以降になります。

テーブルのスキャンコスト低減

よく言われる話として、クエリ(今回のような、テーブルからデータを取り出すSQL文)の実行コストを下げるというのがあるかと思いますが、その際にLIMITを使うのはあまり有効ではないようです。というのも、LIMITはあくまで表示される件数を制限しているだけであり、テーブルのスキャン範囲を制限してはいません。テーブルをスキャンするコストを下げたいのであれば、WHERE句で条件を付けるといった対応が必要となります。 snowflakeの場合は、DBから直接テーブルを選んだ後、テーブルプレビューをすることで上位100件を直接確認できます。

https://dev.classmethod.jp/articles/20200129-snowflake-performance-tuning-7tips/

またそれ以外にも、テーブル情報を確認する方法(describe)や、件数制限をする方法(top n)があります

describe table <table name>;
-- 各カラムとその変数タイプの一覧表示

select top n <column name> from <table name>;
-- 上位n件のみ取り出し

sqlの三値論理について

sqlは、True/Falseの二値論理ではなく、Unknownを含めた三値論理で動いています。例えば、and演算を考えた際には以下のような真理値表になります。

And	True	Unknown	False
True	True	Unknown	False
Unknown	Unknown	Unknown	False
False	False	False	False

or演算の場合には、以下のようになります。

OR	True	Unknown	False
True	True	True	True
Unknown	True	Unknown	Unknown
False	True	Unknown	False

判別方法としては、

unknownをT/Fに入れ替えても結果が同じならその結果
unknownをT/Fに入れ替えて結果が変わるならunknown
となるそうです。

これを注意しなければならない理由としては、where句での例えばINでデータを制限する際、Trueのものしか引っかからないようになっており、Falseとunknownはヒットしません。
なので、nullが入っている場合は、IS NULLなどを使ってそこらへんをうまくやる必要があります。
条件式でwhere column!=NULLとかやっても、論理式結果がunknownになる関係上、IS NULLを使うしかありません。

SQL道場での修行

修行場所はこちらです。

このサイトでは、実行環境を個人で用意する必要がなく、アクセスしたら即座にエディターが開くので、非常に楽です。
ひたすらにクエリを書きまくりましょう。

しょきゅーへん

書きます。ただひたすらに。

初級問題1

問題文
daily_retail_dataテーブルから全てのデータを取得し、日付順に並べ替えて表示してください。

条件
テーブル：daily_retail_data
カラム：＊

回答

select *
from daily_retail_data
order by transaction_date asc;
-- 昇順を明示

解答例

answer

select
  *
from
  daily_retail_data
order by
  transaction_date;

初級問題2

問題文
daily_retail_dataテーブルから業種カテゴリ（business_category）と売上（daily_revenue）のみを取得し、売上の高い順に並べ替えて表示してください。

条件
テーブル：daily_retail_data
カラム：business_category, daily_revenue

回答

select
    business_category,
    daily_revenue
from
    daily_retail_data
order by daily_revenue desc;

解答例

answer

select
  business_category,
  daily_revenue
from
  daily_retail_data
order by
  daily_revenue desc;

初級問題3

問題文
daily_retail_dataテーブルに記録されている取引の総件数を計算してください。

条件
テーブル：daily_retail_data
カラム：すべて
回答

select count(*) as total_records
-- count(*)なら全レコード数を数える　データにnullが入っていても数える
-- count(列指定)ならnullの入ったレコードは数えない
from daily_retail_data;

解答例

answer

select
  count(*) as total_records
from
  daily_retail_data;

初級問題4

問題文
daily_retail_dataテーブルから、売上（daily_revenue）が50000以上の取引データを取得してください。

条件
テーブル：daily_retail_data
カラム：daily_revenue
回答

select *
from daily_retail_data
where daily_revenue  >= 50000 ;
-- where文での絞り込み

解答例

answer

select
  *
from
  daily_retail_data
where
  daily_revenue >= 50000;

初級問題5

問題文
daily_retail_dataテーブルから業種カテゴリ（business_category）と売上（daily_revenue）を取得し、業種カテゴリをアルファベット順に並べ替えて表示してください。

条件
テーブル：daily_retail_data
カラム：business_category, daily_revenue
回答

select business_category, daily_revenue
from daily_retail_data
order by business_category;

解答例

answer

select
  business_category,
  daily_revenue
from
  daily_retail_data
order by
  business_category;

初級問題6

問題文
daily_retail_dataテーブルから、重複のない業種カテゴリ（business_category）の一覧を取得してください。
条件
テーブル：daily_retail_data
カラム：business_category
回答

select distinct business_category
from daily_retail_data;

解答例

answer

select distinct
  business_category
from
  daily_retail_data
order by
  business_category;
-- 問題文で言われてなかったじゃん！！！！

初級問題7

問題文
daily_retail_dataテーブルから、business_categoryに「shop」を含む店舗の取引データを取得し、以下の順で表示してください：

取引日（transaction_date）の昇順
業種カテゴリ（business_category）の昇順

条件
テーブル：daily_retail_data
カラム：transaction_date, business_category, daily_revenue

回答

select transaction_date, business_category, daily_revenue
from daily_retail_data
where business_category like '%shop%';
-- '文字列'で検索 '%文字列%'で、文字列を含むものを検索 
-- '文字列%'なら、文字列から始まるものを検索

解答例

answer

select
  *
from
  daily_retail_data
where
  business_category like '%shop%'
order by
  transaction_date,
  business_category;

初級問題8

問題文
daily_retail_dataテーブルから、daily_revenueがNULLの取引データを全て取得してください。

条件
テーブル：daily_retail_data
カラム：*

回答

select *
from daily_retail_data
where daily_revenue IS NULL;

解答例

answer

select
  *
from
  daily_retail_data
where
  daily_revenue is null;

初級問題9

問題文
daily_retail_dataテーブルの売上（daily_revenue）の最大値と最小値を取得してください。

条件
テーブル：daily_retail_data
カラム：daily_revenue

回答

select MAX(daily_revenue) as 'max_revenue', MIN(daily_revenue) as 'min_revenue'
from daily_retail_data;

解答例

answer

select
  max(daily_revenue) as max_revenue,
  min(daily_revenue) as min_revenue
from
  daily_retail_data;

初級問題10

問題文
daily_retail_dataテーブルから、売上（daily_revenue）が30000以上の取引件数を計算してください。

条件
テーブル：daily_retail_data
カラム：*

回答

select count(*) as high_revenue_count
from daily_retail_data
where daily_revenue >= 30000;

解答例

answer

select
  count(*) as high_revenue_count
from
  daily_retail_data
where
  daily_revenue >= 30000;

初級問題11

問題文
daily_retail_dataテーブルから、売上（daily_revenue）が30000以上50000以下の取引データを取得してください。結果は売上の昇順で表示してください。

条件
テーブル：daily_retail_data
カラム：*

回答

select *
from daily_retail_data
where daily_revenue between 30000 and 50000
order by daily_revenue asc;

解答例

answer

select
  *
from
  daily_retail_data
where
  daily_revenue between 30000 and 50000
order by
  daily_revenue;

初級問題12

問題文
daily_retail_dataテーブルから、2024年1月1日の取引データのみを取得してください。

条件
テーブル：daily_retail_data
カラム：*

回答

select *
from daily_retail_data
where transaction_date = '2024-01-01';

解答例

answer

select
  *
from
  daily_retail_data
where
  transaction_date = '2024-01-01';

初級問題13

問題文
daily_retail_dataテーブルから、業種コード（industry_code）が'RT001'の取引データを取得してください。結果は日付の昇順で表示してください。

条件
テーブル：daily_retail_data
カラム：*

回答

select *
from daily_retail_data
where industry_code = 'RT001'
order by transaction_date;

解答例

answer

select
  *
from
  daily_retail_data
where
  industry_code = 'RT001'
order by
  transaction_date;

初級問題14

問題文

条件
テーブル：daily_retail_data
カラム：daily_revenue

回答

select AVG(daily_revenue) as "avg_revenue"
from daily_retail_data;

解答例

answer

select
  avg(daily_revenue) as avg_revenue
from
  daily_retail_data;

初級問題15

問題文
daily_retail_dataテーブルに含まれる業種カテゴリ（business_category）の種類数を計算してください。

条件
テーブル：daily_retail_data
カラム：business_category

回答

select count(distinct business_category) as category_count
from daily_retail_data;

解答例

answer

select
  count(distinct business_category) as category_count
from
  daily_retail_data;

初級問題16

問題文
daily_retail_dataテーブルから、売上（daily_revenue）が0より大きい取引のみを取得してください。結果は売上の降順で表示してください。

条件
テーブル：daily_retail_data
カラム：*

回答

select *
from daily_retail_data
where daily_revenue > 0
order by daily_revenue desc;

解答例

answer

select
  *
from
  daily_retail_data
where
  daily_revenue > 0
order by
  daily_revenue desc;

初級問題17

問題文
daily_retail_dataテーブルから、業種カテゴリ（business_category）に'Coffee'を含む取引データを取得してください。

条件
テーブル：daily_retail_data
カラム：*

回答

select *
from daily_retail_data
where business_category like '%Coffee%';

解答例

answer

select
  *
from
  daily_retail_data
where
  business_category like '%Coffee%';

初級問題18

問題文
daily_retail_dataテーブルの売上（daily_revenue）の合計値を計算してください。

条件
テーブル：daily_retail_data
カラム：daily_revenue

回答

select sum(daily_revenue) as "total_revenue"
from daily_retail_data;

解答例

answer

select
  sum(daily_revenue) as total_revenue
from
  daily_retail_data;

初級問題19

問題文
daily_retail_dataテーブルから、以下の条件を満たすデータを取得してください：

2024年1月1日以降のデータ
売上（daily_revenue）が40000以上

条件
テーブル：daily_retail_data
カラム：*

回答

select *
from daily_retail_data
where transaction_date >= '2024-01-01' and daily_revenue >= 40000;

解答例

answer

select
  *
from
  daily_retail_data
where
  transaction_date >= '2024-01-01'
  and daily_revenue >= 40000;

初級問題20

問題文
daily_retail_dataテーブルから、取引日（transaction_date）と売上（daily_revenue）のみを取得し、取引日の降順で表示してください。

条件
テーブル：daily_retail_data
カラム：transaction_date, daily_revenue

回答

select transaction_date, daily_revenue
from daily_retail_data
order by transaction_date desc;

解答例

answer

select
  transaction_date,
  daily_revenue
from
  daily_retail_data
order by
  transaction_date desc;

初級編は以上です。長いですね。

ちゅーきゅーへん

書きます。ただひたすらに。

中級問題1

問題文
daily_retail_dataテーブルを使用して、各業種（business_category）の以下の指標を計算してください：

総売上
平均売上
最大売上
最小売上

結果は総売上の降順で表示してください。

条件
テーブル：daily_retail_data
カラム：business_category, daily_revenue

回答

select
    business_category,
    SUM(daily_revenue) AS total_revenue,
    AVG(daily_revenue) AS avg_revenue,
    MAX(daily_revenue) AS max_revenue,
    MIN(daily_revenue) AS min_revenue
    -- 集計関数をかませている
    -- 集計関数を使う場合、group byとセット
    -- select上で集計関数が使われないカラムはgroup byしないとダメ
from
    daily_retail_data
group by
    business_category
-- グループ化するカラムの指定
order by
    total_revenue desc
;

解答例

answer

select
  business_category,
  sum(daily_revenue) as total_revenue,
  round(avg(daily_revenue)) as avg_revenue,
  max(daily_revenue) as max_revenue,
  min(daily_revenue) as min_revenue
from
  daily_retail_data
group by
  business_category
order by
  total_revenue desc;

中級問題2

問題文
daily_retail_dataテーブルから、2024年の月次売上トレンドを分析してください。以下の指標を計算してください：

月間総売上
1日あたりの平均売上
前月比増減率（%）

結果は月順に表示してください。

条件
テーブル：daily_retail_data
カラム：transaction_date, daily_revenue

回答

select
    strftime('%Y-%m', transaction_date) as "year_month",
    -- これで'2024-01-01'から'2024-01'を取り出せるらしい
    -- EXTRACT(year/month from transaction_date)でも抽出は可能だが、月の桁数が2桁に安定的にできなかった
    -- 例 1月が'2024-1となり、次が'2024-10'となってダメ
    SUM(daily_revenue) as "total_revenue",
    AVG(daily_revenue) as "avg_daily_revenue",
    "total_revenue" / LAG("total_revenue") over (order by "year_month") as growth_rate
    -- window関数は、集計関数とは微妙に異なる。
    -- 今回は、ひとつ前の行をとってくるLAGを使用
from
    daily_retail_data
group by 
    "year_month"  
order by
    "year_month"
;

解答例

answer

with -- with句を使って、可読性の向上と事前テーブル読み出し
  monthly_sales as (
    select
      strftime ('%Y-%m', transaction_date) as year_month,
      sum(daily_revenue) as total_revenue,
      avg(daily_revenue) as avg_daily_revenue
    from
      daily_retail_data
    group by
      year_month
  )
select
  year_month,
  total_revenue,
  round(avg_daily_revenue) as avg_daily_revenue,
  -- 丸めている
  round(
    (
      total_revenue - lag(total_revenue) over (
        order by
          year_month
      )
    ) * 100.0 / lag(total_revenue) over (
      order by
        year_month
    ),
    1 -- 小数点以下は一桁にしている
  ) as growth_rate
from
  monthly_sales
order by
  year_month;

感想
普通にwith句を使う発想が抜けてた。
可読性の観点からwith句を使うべき
というか難易度が突然上がりすぎている

中級問題3

問題文
daily_retail_dataテーブルを使用して、各業種の売上構成比を計算してください。以下の指標を含めてください：

総売上
売上構成比（%）
累計構成比（%）

結果は売上構成比の降順で表示してください。

条件
テーブル：daily_retail_data
カラム：business_category, daily_revenue

回答

-- with句で売り上げの合計値の算出
with all_total_revenue as (
    select SUM(daily_revenue)
    from daily_retail_data
), -- with並列はできないので、二つ目以降はwithなし
-- カテゴリーごとの売り上げと割合
sum_revenue_on_category as (
    select
        business_category,
        sum(daily_revenue) as "total_revenue",
        sum(daily_revenue) / (
            select *
            from all_total_revenue
        ) * 100 as "revenue_share"
    from
        daily_retail_data
    group by
        business_category
    order by
        "total_revenue" desc
)
-- 累積割合の計算を、window関数で獲得
select
    business_category,
    total_revenue,
    ROUND("revenue_share",2),
    ROUND(sum(revenue_share) OVER (
        order by revenue_share desc
        ROWS UNBOUNDED PRECEDING -- カンマつなげをするとエラーになる
    ),2) as cumulative_share
from
    sum_revenue_on_category
;

解答例

answer

-- with句でカテゴリごとの売り上げの合計と売り上げ合計
with
  sales_by_category as (
    select
      business_category,
      sum(daily_revenue) as total_revenue
    from
      daily_retail_data
    group by
      business_category
  ),
  total_sales as (
    select
      sum(total_revenue) as grand_total
    from
      sales_by_category
  )
select
  business_category,
  total_revenue,
  round(
    total_revenue * 100.0 / (
      select
        grand_total
      from
        total_sales
    ),
    1
  ) as revenue_share,
  round(
    sum(total_revenue) over (
      order by
        total_revenue desc
    ) * 100.0 / (
      select
        grand_total
      from
        total_sales
    ),-- window関数を使用　使う場合はoverを並べる
    1
  ) as cumulative_share
from
  sales_by_category
order by
  revenue_share desc;

中級問題4

問題文
daily_retail_dataテーブルから、各業種の月次売上推移を分析してください。以下の指標を計算してください：

月間売上
売上構成比（%）
前月からの増加額

結果は業種と年月の順で表示してください。

条件
テーブル：daily_retail_data
カラム：transaction_date, daily_revenue

回答

-- with句で月の売り上げを、カテゴリごとのものとその合計とで作成
with revenue_on_monthly_and_category as (select 
    business_category,
    strftime('%Y-%m', transaction_date) as year_month,
    sum(daily_revenue) as monthly_revenue
from daily_retail_data
group by business_category, year_month
order by business_category, year_month
),
revenue_on_monthly as(
    select
        year_month,
        sum(monthly_revenue) as all_category_monthly_revenue
    from    
        revenue_on_monthly_and_category
    group by
        year_month
)
-- year_monthをキーにしてinner joinすることで、次ごとの売り上げ合計を紐づけ
select
    mc.business_category,
    mc.year_month,
    mc.monthly_revenue,
    mc.monthly_revenue / m.all_category_monthly_revenue as revenue_share,
    mc.monthly_revenue - LAG(mc.monthly_revenue, 1) OVER (PARTITION BY mc.business_category
    order by mc.business_category, mc.year_month) as revenue_increase
from 
    revenue_on_monthly_and_category mc
    inner join revenue_on_monthly m on mc.year_month = m.year_month
order by
    mc.business_category, mc.year_month
;

解答例

answer

with
  monthly_sales as (
    select
      business_category,
      strftime ('%Y-%m', transaction_date) as year_month,
      sum(daily_revenue) as monthly_revenue
    from
      daily_retail_data
    group by
      business_category,
      year_month
  ),
  monthly_total as (
    select
      year_month,
      sum(monthly_revenue) as total_monthly_revenue
    from
      monthly_sales
    group by
      year_month
  )
select
  m.business_category,
  m.year_month,
  m.monthly_revenue,
  round(
    m.monthly_revenue * 100.0 / t.total_monthly_revenue,
    1
  ) as revenue_share,
  m.monthly_revenue - lag(m.monthly_revenue) over (
    partition by
      m.business_category
    order by
      m.year_month
  ) as revenue_increase
from
  monthly_sales m
  join monthly_total t on m.year_month = t.year_month
order by
  m.business_category,
  m.year_month;

中級問題5

問題文
daily_retail_dataテーブルから、曜日ごとの売上パターンを分析してください。以下の指標を計算してください：

平均売上
売上構成比（%）
取引件数

結果は平均売上の降順で表示してください。

条件
テーブル：daily_retail_data
カラム：transaction_date, daily_revenue

回答

with weekly_revenue as (
    SELECT 
        case
            when DAYNAME(transaction_date) = 'Monday' then '月曜日'
            when DAYNAME(transaction_date) = 'Tuesday' then '火曜日'
            when DAYNAME(transaction_date) = 'Wednesday' then '水曜日'
            when DAYNAME(transaction_date) = 'Thursday' then '木曜日'
            when DAYNAME(transaction_date) = 'Friday' then '金曜日'
            when DAYNAME(transaction_date) = 'Saturday' then '土曜日'
            when DAYNAME(transaction_date) = 'Sunday' then '日曜日'
            end as day_of_week,
        AVG(daily_revenue) as "avg_revenue",
        count(*) as transaction_count
    FROM
        daily_retail_data
    group by
        day_of_week
),
    sum_revenue as (
        select
            sum("avg_revenue")
        FROM
            weekly_revenue
    )
select
    day_of_week,
    ROUND(avg_revenue, 2),
    ROUND(avg_revenue / (select * from sum_revenue) * 100, 2) as "revenue_share",
    transaction_count
FROM
    weekly_revenue
order by
    avg_revenue desc
;

解答例

answer

with
  daily_sales as (
    select
      case strftime ('%w', transaction_date)
        when '0' then '日曜日'
        when '1' then '月曜日'
        when '2' then '火曜日'
        when '3' then '水曜日'
        when '4' then '木曜日'
        when '5' then '金曜日'
        when '6' then '土曜日'
      end as day_of_week,
      daily_revenue
    from
      daily_retail_data
  )
select
  day_of_week,
  round(avg(daily_revenue)) as avg_revenue,
  round(
    sum(daily_revenue) * 100.0 / (
      select
        sum(daily_revenue)
      from
        daily_sales
    ),
    1
  ) as revenue_share,
  count(*) as transaction_count
from
  daily_sales
group by
  day_of_week
order by
  avg_revenue desc;

中級問題6

問題文
daily_retail_dataテーブルの売上を以下の区分で分類し、分析してください：

10万円以上：高額
5万円以上10万円未満：中額
5万円未満：少額

各区分について以下の指標を計算してください：

件数
総売上
平均売上

結果は区分の降順で表示してください。

条件
テーブル：daily_retail_data
カラム：daily_revenue

回答

select
    case
        when daily_revenue >= 100000 then '高額'
        when daily_revenue >= 50000 then '中額'
        when daily_revenue <= 50000 then '少額'
        -- else '少額'にしたらNULLなくなった　なぜ
        end as revenue_category,
    count(daily_revenue) as transaction_count,
    sum(daily_revenue) as total_revenue,
    AVG(daily_revenue) as avg_revenue
from
    daily_retail_data
group by
    revenue_category
order by
    avg_revenue desc
;
-- 実行結果
-- 高額 ~~~
-- 中額 ~~~
-- 少額 ~~~
-- NULL ~~
-- なぜかNULL列が出た　謎
-- NULLデータ2つあった

解答例

answer

with
  categorized_sales as (
    select
      case
        when daily_revenue >= 100000 then '高額'
        when daily_revenue >= 50000 then '中額'
        else '少額'
      end as revenue_category,
      daily_revenue
    from
      daily_retail_data
  )
select
  revenue_category,
  count(*) as transaction_count,
  sum(daily_revenue) as total_revenue,
  round(avg(daily_revenue)) as avg_revenue
from
  categorized_sales
group by
  revenue_category
order by -- case文で直接順序指定ができる
  case revenue_category
    when '高額' then 1
    when '中額' then 2
    else 3
  end;

感想
データ中のNULLを許すな

中級問題7

問題文

条件
テーブル：daily_retail_data
カラム：daily_revenue

回答

select
    strftime('%Y-%m', transaction_date) as year_month,
    sum(daily_revenue) as monthly_revenue,
    sum(daily_revenue) / 200000 as achievement_rate
from
    daily_retail_data
group by
    year_month
order by
    year_month
;

解答例

answer

with
  monthly_sales as (
    select
      strftime ('%Y-%m', transaction_date) as year_month,
      sum(daily_revenue) as monthly_revenue
    from
      daily_retail_data
    group by
      year_month
  )
select
  year_month,
  monthly_revenue,
  round(monthly_revenue * 100.0 / 2000000000, 1) as achievement_rate
from
  monthly_sales
order by
  year_month;

中級問題8

問題文
daily_retail_dataテーブルから、業種ごとの週次平均売上を分析してください。以下の指標を計算してください：

週次平均売上
週次最大売上
週次最小売上

結果は業種と週の順で表示してください。

条件
テーブル：daily_retail_data
カラム：transaction_date, daily_revenue

回答

select
    business_category,
    strftime ('%U', transaction_date) as week_number,
    -- %Uで週数を00~53で取得可能
    AVG(daily_revenue) as avg_weekly_revenue,
    MAX(daily_revenue) as max_weekly_revenue,
    MIN(daily_revenue) as min_weekly_revenue
from daily_retail_data
group by business_category, week_number
order by business_category, week_number;

解答例

answer

select
  business_category,
  strftime ('%W', transaction_date) as week_number,
  round(avg(daily_revenue)) as avg_weekly_revenue,
  max(daily_revenue) as max_weekly_revenue,
  min(daily_revenue) as min_weekly_revenue
from
  daily_retail_data
group by
  business_category,
  week_number
order by
  business_category,
  cast(week_number as integer);
  -- cast (column as 変数タイプ)　で変換できるっぽい

感想
週数って取得できるようになってるんだ・・・

中級問題9

問題文
daily_retail_dataテーブルを使用して、業種ごとの7日間移動平均売上を計算してください。以下の指標を含めてください：

当日売上
7日間移動平均
移動平均からの乖離率（%）

結果は日付順に表示してください。

条件
テーブル：daily_retail_data
カラム：business_category, transaction_date, daily_revenue

回答

with daily_revenue_on_category as (
    select
        business_category,
        transaction_date,
        sum(daily_revenue) as sum_daily_revenue,
        ROUND(AVG(sum_daily_revenue) over (partition by business_category
        order by transaction_date rows between 3 PRECEDING and 3 FOLLOWING),2)
        as moving_avg_7d
    from
        daily_retail_data
    group by
        business_category,
        transaction_date
    order by
        business_category,
        transaction_date
)
select
    business_category,
    transaction_date,
    sum_daily_revenue as "daily_revenue",
    moving_avg_7d,
    ROUND(("daily_revenue" - moving_avg_7d) / moving_avg_7d * 100, 2) as "deviation_rate"
from
    daily_revenue_on_category
order by
    business_category, transaction_date
;

解答例

answer

select
  business_category,
  transaction_date,
  daily_revenue,
  round(
    avg(daily_revenue) over (
      partition by
        business_category
      order by
        transaction_date rows between 6 preceding
        and current ROW
    )
  ) as moving_avg_7d,
  round(
    (
      daily_revenue - avg(daily_revenue) over (
        partition by
          business_category
        order by
          transaction_date rows between 6 preceding
          and current ROW
      )
    ) * 100.0 / avg(daily_revenue) over (
      partition by
        business_category
      order by
        transaction_date rows between 6 preceding
        and current ROW
    ),
    1
  ) as deviation_rate
from
  daily_retail_data
order by
  business_category,
  transaction_date;

感想
普通にこれ解答例にある移動平均を求める部分はwith句でやった方がいいはず
可読性がない

中級問題10

問題文
daily_retail_dataテーブルを使用して、各業種の売上変動を分析してください。以下の指標を計算してください：

平均売上
売上の標準偏差
最大売上と最小売上の差（売上範囲）
変動係数（標準偏差/平均）

結果は変動係数の降順で表示してください。

条件
テーブル：daily_retail_data
カラム：business_category, daily_revenue

回答

select
    business_category,
    AVG(daily_revenue) as avg_revenue,
    stddev(daily_revenue) as std_dev,
    MAX(daily_revenue) - MIN(daily_revenue) as revenue_range,
    ROUND(stddev(daily_revenue) / AVG(daily_revenue), 2) as cv
from
    daily_retail_data
group by
    business_category
order by
    cv desc
;

解答例

answer

select
  business_category,
  round(avg(daily_revenue)) as avg_revenue,
  round(stddev(daily_revenue)) as std_dev,
  max(daily_revenue) - min(daily_revenue) as revenue_range,
  round(stddev(daily_revenue) / avg(daily_revenue), 2) as cv
from
  daily_retail_data
group by
  business_category
order by
  cv desc;

GPTに追加で問題を作らせよう！

追加で作らせました。

問題作成プロンプト

sqlの演習を行っています。あなたには、次のテーブルデータから、問題を作成してもらいたいです。その際、whereやgroup byを使うだけで終わる単純な問題ではなく、テーブル結合やウィンドウ関数の使用が必要となる、複雑な問題を作成してください。

使用するテーブルは以下の通りです。
テーブル名：daily_retail_data
カラム1：transaction_date　取引日（YYYY-MM-DD形式）
カラム2：industry_code　業界コード（例：RT001）
カラム3：business_category　ビジネスカテゴリ名（例：Supermarket chains, Fashion outlets）
カラム４：daily_revenue　日次売上高

GPT問題1

【問題文】
あなたは小売業界のデータアナリストです。業界コード（industry_code）ごとの売上推移を分析するために、以下の条件を満たすレポートを作成してください。

【要件】
各業界（industry_code）について、月ごとの合計売上（monthly_revenue）を算出しなさい。

その月の売上が、同業界における過去3か月間の売上平均より高いかどうかを判定しなさい。

各行には以下の列を含めなさい：

industry_code
month（YYYY-MM形式）
monthly_revenue（月間売上）
avg_revenue_last_3_months（過去3か月の平均売上）
is_above_average（monthly_revenue > avg_revenue_last_3_monthsならTRUE、そうでなければFALSE）

条件
テーブル：daily_retail_data
カラム：industry_code, daily_revenue, transaction_date

回答

with monthly_revenue as (select
    industry_code,
    strftime('%Y-%m', transaction_date) as "month",
    sum(daily_revenue) as monthly_revenue
from
    daily_retail_data
group by
    industry_code, "month"
)
select
    industry_code,
    "month",
    monthly_revenue,
    ROUND(AVG(monthly_revenue) over (
        PARTITION BY industry_code 
        order by "month" 
        ROWS between 3 preceding
        and 1 preceding), 2)
    as avg_revenue_last_3_months,
    case
        when monthly_revenue > avg_revenue_last_3_months then TRUE
        else FALSE
    end as "is_above_average"

from
    monthly_revenue
order by
    industry_code, "month"
;

解答例(GPT4o作成)

answer

WITH monthly_revenue AS (
    SELECT
        industry_code,
        strftime('%Y-%m', transaction_date) AS month,
        SUM(daily_revenue) AS monthly_revenue
    FROM
        daily_retail_data
    GROUP BY
        industry_code, month
),
with_avg AS (
    SELECT
        industry_code,
        month,
        monthly_revenue,
        ROUND(AVG(monthly_revenue) OVER (
            PARTITION BY industry_code
            ORDER BY month
            ROWS BETWEEN 3 PRECEDING AND 1 PRECEDING
        ), 2) AS avg_revenue_last_3_months
    FROM
        monthly_revenue
)
SELECT
    industry_code,
    month,
    monthly_revenue,
    avg_revenue_last_3_months,
    CASE
        WHEN monthly_revenue > avg_revenue_last_3_months THEN TRUE
        ELSE FALSE
    END AS is_above_average
FROM
    with_avg
ORDER BY
    industry_code, month;

GPT問題2

【問題文】
あなたは小売業界のデータ分析担当者です。業界ごとの売上傾向の変化を把握するため、以下の分析を行ってください。

【要件】
各業界（industry_code）ごとに、直近2か月間（最新月とその前の月）の売上高を比較し、「売上が急増した業界（増加率20%以上）」を抽出してください。
比較対象の2か月は、データの中で最新の月と、その1つ前の月とします。
出力する列は以下の通り：

industry_code
prev_month（前の月：YYYY-MM）
prev_month_revenue
latest_month（最新の月：YYYY-MM）
latest_month_revenue
growth_rate（前月比増加率、％）
is_significant_growth（増加率が20%以上ならTRUE）

条件
テーブル：daily_retail_data
カラム：industry_code, transaction_date, daily_revenue

回答

with monthly_revenue as (
    select
        industry_code,
        strftime('%Y-%m', transaction_date) as "month",
        sum(daily_revenue) as month_revenue
    from
        daily_retail_data
    group by
        industry_code, "month"
    order by
        industry_code, "month"
)
select
    industry_code,
    LAG("month",1) over (
        PARTITION BY industry_code
        ORDER BY "month"
    ) as "prev_month",
    LAG("month_revenue",1) over (
        PARTITION BY industry_code
        ORDER BY "month"
    ) as "prev_month_revenue",
    "month" as latest_month,
    month_revenue as latest_month_revenue,
    (month_revenue - LAG(month_revenue,1) over (
        PARTITION BY industry_code
        ORDER BY "month"
    )) / LAG(month_revenue,1) over (
        PARTITION BY industry_code
        ORDER BY "month"
    ) * 100 as growth_rate,
    case
        when growth_rate >= 20 then TRUE
        else FALSE
    end as "is_significant_growth"
from
    monthly_revenue
order by
    industry_code, "month"
;

解答例

answer

WITH monthly_revenue AS (
    SELECT
        industry_code,
        strftime('%Y-%m', transaction_date) AS month,
        SUM(daily_revenue) AS month_revenue
    FROM
        daily_retail_data
    GROUP BY
        industry_code, month
),
with_growth AS (
    SELECT
        industry_code,
        month AS latest_month,
        month_revenue AS latest_month_revenue,
        LAG(month) OVER (PARTITION BY industry_code ORDER BY month) AS prev_month,
        LAG(month_revenue) OVER (PARTITION BY industry_code ORDER BY month) AS prev_month_revenue,
        ROUND(
            (month_revenue - LAG(month_revenue) OVER (PARTITION BY industry_code ORDER BY month)) * 100.0
            / LAG(month_revenue) OVER (PARTITION BY industry_code ORDER BY month), 2
        ) AS growth_rate
    FROM
        monthly_revenue
),
latest_2_months AS (
    SELECT
        *,
        ROW_NUMBER() OVER (PARTITION BY industry_code ORDER BY latest_month DESC) AS rn
    FROM
        with_growth
)
SELECT
    industry_code,
    prev_month,
    prev_month_revenue,
    latest_month,
    latest_month_revenue,
    growth_rate,
    CASE
        WHEN growth_rate >= 20 THEN TRUE
        ELSE FALSE
    END AS is_significant_growth
FROM
    latest_2_months
WHERE
    rn = 1  -- 最新月のみ出力
ORDER BY
    industry_code;

まとめ

ひとまず、実装されている問題+αを一通り片付けきりました。
ウィンドウ関数とテーブルの結合の部分をあまりできなかったので、そこは別途演習をしていきたい所存です。
次は、追加問題snowflakeを使った演習の方をやっていきたいと思います。

参考