【SQL, Docker, Github】初心者向けのTIP集~データサイエンス100本ノック~

""" S-053: 顧客テーブル（customer）の郵便番号（postal_cd）に対し、東京（先頭3桁が100〜209のもの）を1、それ以外のものを0に２値化せよ。さらにレシート明細テーブル（receipt）と結合し、全期間において買い物実績のある顧客数を、作成した2値ごとにカウントせよ。"""
%%sql
WITH customer_postal as (
    SELECT 
        customer_id,
        postal_cd,
        CASE 
            WHEN CAST(SUBSTR(postal_cd, 1, 3) AS INTEGER) >= 100
                and CAST(SUBSTR(postal_cd, 1, 3) AS INTEGER) <= 209 THEN 1
            ELSE 0
        END AS postal_flg
    FROM
        customer
),
receipt_sum as (
    SELECT customer_id, sum(amount)
    FROM receipt
    GROUP BY customer_id
)
SELECT
    cp.postal_flg, count(1)
FROM customer_postal as cp
INNER JOIN receipt_sum as rs ON cp.customer_id = rs.customer_id
GROUP BY cp.postal_flg

""" S-036: レシート明細テーブル（receipt）と店舗テーブル（store）を内部結合し、レシート明細テーブルの全項目と店舗テーブルの店舗名（store_name）を10件表示させよ。 """
%%sql
SELECT
    re.*, st.store_name
FROM
    receipt as re
INNER JOIN store as st ON re.store_cd = st.store_cd
LIMIT 10    

""" # S-052: レシート明細テーブル（receipt）の売上金額（amount）を顧客ID（customer_id）ごとに合計の上、売上金額合計に対して2000円以下を0、2000円超を1に2値化し、顧客ID、合計金額とともに10件表示せよ。ただし、顧客IDが"Z"から始まるのものは非会員を表すため、除外して計算すること。"""
%%sql
SELECT 
    customer_id,
    sum(amount),
    CASE
        WHEN SUM(amount) >= 2000 THEN 1
        ELSE 0
    END
FROM receipt
WHERE customer_id not like 'Z%'
GROUP BY customer_id
LIMIT 10

""" S-094: 先に作成したカテゴリ名付き商品データを以下の仕様でファイル出力せよ。出力先のパスは'/tmp/data'を指定することでJupyterの/'work/data'と共有されるようになっている。なお、COPYコマンドの権限は付与済みである。 """
%%sql
COPY product_full TO '/tmp/data/S_product_full_UTF-8_header.csv' WITH CSV HEADER encoding 'UTF-8'

""" S-099: 先に作成したカテゴリ名付き商品データを以下の仕様でファイル出力せよ。出力先のパスは'/tmp/data'を指定することでJupyterの/'work/data'と共有されるようになっている。なお、COPYコマンドの権限は付与済みである。 """
%%sql
COPY product_full TO '/tmp/data/S_product_full_UTF-8_header.tsv' WITH CSV HEADER DELIMITER E'\t' encoding 'UTF-8'

""" S-031: レシート明細テーブル（receipt）に対し、店舗コード（store_cd）ごとに売上金額（amount）の標本標準偏差を計算し、降順にTOP5を表示せよ。 """
%%sql
SELECT store_cd,
    ROUND(STDDEV_SAMP(amount), 1) as std_sample
FROM receipt
GROUP BY store_cd
ORDER BY std_sample DESC
LIMIT 5

""" S-028: レシート明細テーブル（receipt）に対し、店舗コード（store_cd）ごとに売上金額（amount）の中央値を計算し、降順でTOP5を表示せよ。 """
%%sql
SELECT store_cd,
   PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY amount) as median
FROM receipt
GROUP BY store_cd
ORDER BY median
LIMIT 5

""" S-038: 顧客テーブル（customer）とレシート明細テーブル（receipt）から、各顧客ごとの売上金額合計を求めよ。ただし、買い物の実績がない顧客については売上金額を0として表示させること。また、顧客は性別コード（gender_cd）が女性（1）であるものを対象とし、非会員（顧客IDが'Z'から始まるもの）は除外すること。なお、結果は10件だけ表示させれば良い。 """
%%sql
WITH customer_amount as (
    SElECT
        customer_id,
        SUM(amount) as sum_amount
    FROM 
        receipt
    GROUP BY customer_id
)
SELECT
    cu.customer_id,
    COALESCE(ca.sum_amount, 0)
FROM 
    customer as cu
LEFT JOIN customer_amount as ca 
    ON cu.customer_id = ca.customer_id
WHERE 
    cu.customer_id not like 'Z%' and cu.gender_cd = '1'
LIMIT 10

""" S-039: レシート明細テーブル（receipt）から売上日数の多い顧客の上位20件と、売上金額合計の多い顧客の上位20件を抽出し、完全外部結合せよ。ただし、非会員（顧客IDが'Z'から始まるもの）は除外すること。 """
%%sql
WITH customer_sales_days as (
    SELECT customer_id, count(distinct sales_ymd) as count_days
    FROM receipt
    WHERE customer_id not like 'Z%'
    GROUP BY customer_id
    ORDER BY count_days DESC    
),
customer_amount as (
    SELECT customer_id, sum(amount) as sum_amount
    FROM receipt
    WHERE customer_id not like 'Z%'
    GROUP BY customer_id
    ORDER BY sum_amount DESC
)
SELECT
    COALESCE(cs.customer_id, ca.customer_id), cs.count_days, ca.sum_amount
FROM
    customer_sales_days as cs
FULL JOIN customer_amount as ca
    ON cs.customer_id = ca.customer_id
LIMIT 10

""" S-041: レシート明細テーブル（receipt）の売上金額（amount）を日付（sales_ymd）ごとに集計し、前日からの売上金額増減を計算せよ。なお、計算結果は10件表示すればよい。 """
%%sql
WITH customer_amount as (
    SELECT 
        sales_ymd, sum(amount) as sum_amount
    FROM receipt
    GROUP BY sales_ymd
)
SELECT
    ca.sales_ymd,
    LAG (ca.sales_ymd, 1) OVER (ORDER BY ca.sales_ymd) as lag_days,
    ca.sum_amount,
    LAG (ca.sum_amount, 1) OVER (ORDER BY ca.sales_ymd) as lag_amount,
    ca.sum_amount - LAG (ca.sum_amount, 1) OVER (ORDER BY ca.sales_ymd) as amount_diff
FROM customer_amount as ca
LIMIT 10

""" S-045: 顧客テーブル（customer）の生年月日（birth_day）は日付型（Date）でデータを保有している。これをYYYYMMDD形式の文字列に変換し、顧客ID（customer_id）とともに抽出せよ。データは10件を抽出すれば良い。 """
%%sql
SELECT 
    customer_id,
    TO_CHAR(birth_day, 'YYYYMMDD') as birth_day 
FROM customer 
LIMIT 10

""" S-070: レシート明細テーブル（receipt）の売上日（sales_ymd）に対し、顧客テーブル（customer）の会員申込日（application_date）からの経過日数を計算し、顧客ID（customer_id）、売上日、会員申込日とともに表示せよ。結果は10件表示させれば良い（なお、sales_ymdは数値、application_dateは文字列でデータを保持している点に注意）。 """
%%sql
WITH receipt_distinct as (
    SELECT distinct
        customer_id,
        sales_ymd    
    FROM receipt
)
SELECT
    cu.customer_id,
    rd.sales_ymd as sales_date,
    cu.application_date as apply_date,
    EXTRACT(DAY FROM (TO_TIMESTAMP(CAST(rd.sales_ymd as VARCHAR), 'YYYYMMDD')
                    - TO_TIMESTAMP(cu.application_date, 'YYYYMMDD')))
FROM
   receipt_distinct as rd 
JOIN customer as cu ON cu.customer_id = rd.customer_id
LIMIT 10

""" S-047: レシート明細テーブル（receipt）の売上日（sales_ymd）はYYYYMMDD形式の数値型でデータを保有している。これを日付型（dateやdatetime）に変換し、レシート番号(receipt_no)、レシートサブ番号（receipt_sub_no）とともに抽出せよ。データは10件を抽出すれば良い。 """
%%sql
SELECT 
    TO_DATE(CAST(sales_ymd AS varchar), 'YYYYMMDD'), 
    receipt_no, 
    receipt_sub_no 
FROM receipt 
LIMIT 10

""" S-049: レシート明細テーブル（receipt）の販売エポック秒（sales_epoch）を日付型（timestamp型）に変換し、"年"だけ取り出してレシート番号(receipt_no)、レシートサブ番号（receipt_sub_no）とともに抽出せよ。データは10件を抽出すれば良い。"""
%%sql
SELECT 
    TO_CHAR(EXTRACT(YEAR FROM TO_TIMESTAMP(sales_epoch)),'FM9999') as sales_year,
    receipt_no,
    receipt_sub_no
FROM receipt
LIMIT 10

""" S-053: 顧客テーブル（customer）の郵便番号（postal_cd）に対し、東京（先頭3桁が100〜209のもの）を1、それ以外のものを0に２値化せよ。さらにレシート明細テーブル（receipt）と結合し、全期間において買い物実績のある顧客数を、作成した2値ごとにカウントせよ。"""
%%sql
WITH customer_postal as (
    SELECT 
        customer_id,
        postal_cd,
        CASE 
            WHEN CAST(SUBSTR(postal_cd, 1, 3) AS INTEGER) >= 100
                and CAST(SUBSTR(postal_cd, 1, 3) AS INTEGER) <= 209 THEN 1
            ELSE 0
        END AS postal_flg
    FROM
        customer
),
receipt_sum as (
    SELECT customer_id, sum(amount)
    FROM receipt
    GROUP BY customer_id
)
SELECT
    cp.postal_flg, count(1)
FROM customer_postal as cp
INNER JOIN receipt_sum as rs ON cp.customer_id = rs.customer_id
GROUP BY cp.postal_flg

""" S-056: 顧客テーブル（customer）の年齢（age）をもとに10歳刻みで年代を算出し、顧客ID（customer_id）、生年月日（birth_day）とともに抽出せよ。ただし、60歳以上は全て60歳代とすること。年代を表すカテゴリ名は任意とする。先頭10件を表示させればよい。df_store.query("tel_no.str.contains('^[0-9]{3}-[0-9]{3}-[0-9]{4}$')", engine='python')"""
%%sql
SELECT 
    customer_id,
    birth_day,
    LEAST(CAST(TRUNC(age/10)*10 AS INTEGER), 60) as era
FROM
    customer
GROUP BY customer_id, birth_day
ORDER BY era desc
LIMIT 10

""" S-067: 商品テーブル（product）の各商品について、利益率が30%となる新たな単価を求めよ。今回は、1円未満を切り上げること。そして結果を10件表示させ、利益率がおよそ30％付近であることを確認せよ。ただし、単価（unit_price）と原価（unit_cost）にはNULLが存在することに注意せよ。"""
%%sql
SELECT
    product_cd,
    unit_price,
    unit_cost,
    CEIL(unit_cost / 0.7) as new_price,
    (CEIL(unit_cost / 0.7) - unit_cost) / CEIL(unit_cost / 0.7) as new_ratio
FROM product
LIMIT 10

""" S-056: 顧客テーブル（customer）の年齢（age）をもとに10歳刻みで年代を算出し、顧客ID（customer_id）、生年月日（birth_day）とともに抽出せよ。ただし、60歳以上は全て60歳代とすること。年代を表すカテゴリ名は任意とする。先頭10件を表示させればよい。"""
%%sql
SELECT 
    customer_id,
    birth_day,
    LEAST(CAST(TRUNC(age/10)*10 AS INTEGER), 60) as era
FROM
    customer
GROUP BY customer_id, birth_day
ORDER BY era desc
LIMIT 10

""" S-057: 前問題の抽出結果と性別（gender）を組み合わせ、新たに性別×年代の組み合わせを表すカテゴリデータを作成せよ。組み合わせを表すカテゴリの値は任意とする。先頭10件を表示させればよい。"""
%%sql
SELECT 
    customer_id,
    birth_day,
    CONCAT(gender_cd, '-', LEAST(CAST(TRUNC(age/10)*10 AS INTEGER), 60)) as era
FROM
    customer
GROUP BY customer_id, birth_day
ORDER BY era
LIMIT 10

""" S-061: レシート明細テーブル（receipt）の売上金額（amount）を顧客ID（customer_id）ごとに合計し、合計した売上金額を常用対数化（底=10）して顧客ID、売上金額合計とともに表示せよ。ただし、顧客IDが"Z"から始まるのものは非会員を表すため、除外して計算すること。結果は10件表示させれば良い。"""
%%sql
WITH sales_amount as (
SELECT 
    customer_id,
    sum(amount) as sum_amount
FROM receipt
WHERE customer_id not like 'Z%'
GROUP BY customer_id
)
SELECT
    customer_id,
    sum_amount,
    LOG10(sum_amount+1)
FROM sales_amount
LIMIT 10

""" S-075: 顧客テーブル（customer）からランダムに1%のデータを抽出し、先頭から10件データを抽出せよ。"""
%%sql
SELECT * 
FROM customer 
WHERE RANDOM() <= 0.01
LIMIT 10