SQLデータサイエンス100本ノック(記録)61〜80

問６１　

S-061: レシート明細テーブル（receipt）の売上金額（amount）を顧客ID（customer_id）ごとに合計し、合計した売上金額を常用対数化（底=10）して顧客ID、売上金額合計とともに表示せよ。ただし、顧客IDが"Z"から始まるのものは非会員を表すため、除外して計算すること。結果は10件表示させれば良い。

SELECT
    customer_id,
    SUM(amount),
    LOG(SUM(amount) + 1) AS log_amount
FROM
    receipt
WHERE
    customer_id NOT LIKE 'Z%'
GROUP BY
    customer_id
LIMIT 10;

問６２　

S-062: レシート明細テーブル（receipt）の売上金額（amount）を顧客ID（customer_id）ごとに合計し、合計した売上金額を自然対数化(底=e）して顧客ID、売上金額合計とともに表示せよ（ただし、顧客IDが"Z"から始まるのものは非会員を表すため、除外して計算すること）。結果は10件表示させれば良い。

SELECT
    customer_id,
    SUM(amount),
    LN(SUM(amount) + 1) AS log_amount
FROM
    receipt
WHERE
    customer_id NOT LIKE 'Z%'
GROUP BY
    customer_id
LIMIT 10;

問６３　

S-063: 商品テーブル（product）の単価（unit_price）と原価（unit_cost）から、各商品の利益額を算出せよ。結果は10件表示させれば良い。

SELECT
    product_cd, 
    unit_price, 
    unit_cost,
    unit_price - unit_cost AS unit_profit
FROM
    product
LIMIT 10;

問６４　

S-064: 商品テーブル（product）の単価（unit_price）と原価（unit_cost）から、各商品の利益率の全体平均を算出せよ。 ただし、単価と原価にはNULLが存在することに注意せよ。

SELECT AVG((unit_price * 1.0 - unit_cost) / unit_price) AS unit_profit_rate
FROM product
LIMIT 10;

問６５　

S-065: 商品テーブル（product）の各商品について、利益率が30%となる新たな単価を求めよ。ただし、1円未満は切り捨てること。そして結果を10件表示させ、利益率がおよそ30％付近であることを確認せよ。ただし、単価（unit_price）と原価（unit_cost）にはNULLが存在することに注意せよ。

SELECT
    product_cd, 
    unit_price, 
    unit_cost,
    TRUNC(unit_cost / 0.7) as new_price,
    ((TRUNC(unit_cost / 0.7) - unit_cost) / TRUNC(unit_cost / 0.7)) 
AS new_profit
FROM product
LIMIT 10;  

問６６　

S-066: 商品テーブル（product）の各商品について、利益率が30%となる新たな単価を求めよ。今回は、1円未満を四捨五入すること。そして結果を10件表示させ、利益率がおよそ30％付近であることを確認せよ。ただし、単価（unit_price）と原価（unit_cost）にはNULLが存在することに注意せよ。

SELECT
    product_cd, 
    unit_price, 
    unit_cost,
    ROUND(unit_cost / 0.7) AS new_price,
    ((ROUND(unit_cost / 0.7) - unit_cost) / ROUND(unit_cost / 0.7)) AS new_profit
FROM
    product
LIMIT 10 

問６７

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

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_profit
FROM product
LIMIT 10; 

問６８

S-068: 商品テーブル（product）の各商品について、消費税率10%の税込み金額を求めよ。 1円未満の端数は切り捨てとし、結果は10件表示すれば良い。ただし、単価（unit_price）にはNULLが存在することに注意せよ。

SELECT
    product_cd,
    unit_price,
    TRUNC(unit_price * 1.1) AS tax_price
FROM product
LIMIT 10;

問６９　

S-069: レシート明細テーブル（receipt）と商品テーブル（product）を結合し、(1)顧客毎に全商品の売上金額合計と、(2)カテゴリ大区分（category_major_cd）が"07"（瓶詰缶詰）の売上金額合計を計算の上、両者の比率を求めよ。抽出対象はカテゴリ大区分"07"（瓶詰缶詰）の購入実績がある顧客のみとし、結果は10件表示させればよい。

WITH amount_all AS(
    SELECT
        customer_id,
        sum(amount) AS sum_all
    FROM
        receipt
    GROUP BY
        customer_id
),
amount_07 AS (
    SELECT
        r.customer_id,
        SUM(r.amount) AS sum_07
    FROM
        receipt r
    JOIN
        product p
    ON
        r.product_cd = p.product_cd 
        AND  p.category_major_cd = '07'
    GROUP BY
        customer_id
)
SELECT
    amount_all.customer_id,
    sum_all,
    sum_07,
    sum_07 * 1.0 / sum_all as sales_rate
FROM
    amount_all
JOIN
    amount_07
ON
    amount_all.customer_id = amount_07.customer_id
LIMIT 10;

問７０　

S-070: レシート明細テーブル（receipt）の売上日（sales_ymd）に対し、顧客テーブル（customer）の会員申込日（application_date）からの経過日数を計算し、顧客ID（customer_id）、売上日、会員申込日とともに表示せよ。結果は10件表示させれば良い（なお、sales_ymdは数値、application_dateは文字列でデータを保持している点に注意）

WITH receit_distinct AS (
    SELECT DISTINCT
        customer_id,
        sales_ymd
    FROM
        receipt
)
SELECT
    c.customer_id,
    r.sales_ymd,
    c.application_date,
    EXTRACT(DAY FROM (TO_TIMESTAMP(CAST(r.sales_ymd AS VARCHAR), 'YYYYMMDD') 
                      - TO_TIMESTAMP(c.application_date, 'YYYYMMDD'))) AS elapsed_days
FROM
    receit_distinct r
JOIN
    customer c
ON
    r.customer_id = c.customer_id
LIMIT 10;

問７１　

S-071: レシート明細テーブル（receipt）の売上日（sales_ymd）に対し、顧客テーブル（customer）の会員申込日（application_date）からの経過月数を計算し、顧客ID（customer_id）、売上日、会員申込日とともに表示せよ。結果は10件表示させれば良い（なお、sales_ymdは数値、application_dateは文字列でデータを保持している点に注意）。1ヶ月未満は切り捨てること。

WITH receit_distinct AS (
    SELECT distinct
        customer_id,
        sales_ymd
    FROM
        receipt
),
time_age_tbl AS(
    SELECT
        c.customer_id,
        r.sales_ymd,
        c.application_date,
        AGE(TO_TIMESTAMP(CAST(r.sales_ymd AS VARCHAR), 'YYYYMMDD'),
                          TO_TIMESTAMP(c.application_date, 'YYYYMMDD')) AS time_age
    FROM
        receit_distinct r
    JOIN
        customer c
    ON
        r.customer_id = c.customer_id
)
SELECT customer_id, sales_ymd, application_date,
    extract(year from time_age) * 12 + extract(month from time_age) AS elapsed_months
FROM
    time_age_tbl
LIMIT 10

問７２　

S-072: レシート明細テーブル（receipt）の売上日（sales_ymd）に対し、顧客テーブル（customer）の会員申込日（application_date）からの経過年数を計算し、顧客ID（customer_id）、売上日、会員申込日とともに表示せよ。結果は10件表示させれば良い（なお、sales_ymdは数値、application_dateは文字列でデータを保持している点に注意）。1年未満は切り捨てること。

WITH receit_distinct AS (
    SELECT DISTINCT
        customer_id,
        sales_ymd
    FROM
        receipt
)
SELECT
    c.customer_id,
    r.sales_ymd,
    c.application_date,
    EXTRACT(YEAR FROM AGE(TO_TIMESTAMP(CAST(r.sales_ymd AS VARCHAR), 'YYYYMMDD'), 
                      TO_TIMESTAMP(c.application_date, 'YYYYMMDD'))) AS elapsed_years
FROM
    receit_distinct r
JOIN
    customer c
ON
    r.customer_id = c.customer_id
LIMIT 10

問７３　

S-073: レシート明細テーブル（receipt）の売上日（sales_ymd）に対し、顧客テーブル（customer）の会員申込日（application_date）からのエポック秒による経過時間を計算し、顧客ID（customer_id）、売上日、会員申込日とともに表示せよ。結果は10件表示させれば良い（なお、sales_ymdは数値、application_dateは文字列でデータを保持している点に注意）。なお、時間情報は保有していないため各日付は0時0分0秒を表すものとする

WITH receit_distinct AS (
    SELECT distinct
        customer_id,
        sales_ymd
    FROM
        receipt
)
SELECT
    c.customer_id,
    r.sales_ymd,
    c.application_date,
    EXTRACT(EPOCH FROM TO_TIMESTAMP(CAST(r.sales_ymd AS VARCHAR), 'YYYYMMDD')) - 
                      EXTRACT(EPOCH FROM TO_TIMESTAMP(c.application_date, 'YYYYMMDD')) AS elapsed_epoch
FROM
    receit_distinct r
JOIN
    customer c
ON
    r.customer_id = c.customer_id
LIMIT 10

問７４　

S-074: レシート明細テーブル（receipt）の売上日（sales_ymd）に対し、当該週の月曜日からの経過日数を計算し、売上日、当該週の月曜日付とともに表示せよ。結果は10件表示させれば良い（なお、sales_ymdは数値でデータを保持している点に注意）

SELECT
    customer_id,
    TO_DATE(CAST(sales_ymd AS VARCHAR), 'YYYYMMDD'),
    EXTRACT(DOW FROM (TO_DATE(CAST(sales_ymd AS VARCHAR), 'YYYYMMDD') - 1)) AS elapsed_years,
    TO_DATE(CAST(sales_ymd AS VARCHAR), 'YYYYMMDD')
        - CAST(EXTRACT(DOW FROM (TO_DATE(CAST(sales_ymd AS VARCHAR), 'YYYYMMDD') - 1)) AS INTEGER) AS monday
FROM
    receipt
LIMIT 10;

問７５　

S-075: 顧客テーブル（customer）からランダムに1%のデータを抽出し、先頭から10件データを抽出せよ。

SELECT * FROM customer WHERE RANDOM() <= 0.01
LIMIT 10;

問７６　

S-076: 顧客テーブル（customer）から性別（gender_cd）の割合に基づきランダムに10%のデータを層化抽出データし、性別ごとに件数を集計せよ。

WITH cusotmer_random AS (
    SELECT customer_id, g_cd, cnt
    FROM (
        SELECT
            ARRAY_AGG(customer ORDER BY RANDOM()) AS customer_r, 
            gender_cd AS g_cd, 
            COUNT(1) AS cnt
        FROM
            customer
        GROUP BY
            gender_cd
    )sample, UNNEST(customer_r)
),
cusotmer_rownum AS(
    SELECT * , ROW_NUMBER() OVER(PARTITION BY g_cd) AS rn
    FROM cusotmer_random
)
SELECT
    g_cd, 
    COUNT(1)
FROM
    cusotmer_rownum
WHERE rn <= cnt * 0.1
GROUP BY g_cd;

問７７　

S-077: レシート明細テーブル（receipt）の売上金額（amount）を顧客単位に合計し、合計した売上金額の外れ値を抽出せよ。ただし、顧客IDが"Z"から始まるのものは非会員を表すため、除外して計算すること。なお、ここでは外れ値を平均から3σ以上離れたものとする。結果は10件表示させれば良い。

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
FROM sales_amount
CROSS JOIN (
    SELECT AVG(sum_amount) AS avg_amount, STDDEV_SAMP(sum_amount) AS std_amount
    FROM sales_amount   
) stats_amount
WHERE ABS(sum_amount - avg_amount) / std_amount > 3
LIMIT 10;

問７８　

S-078: レシート明細テーブル（receipt）の売上金額（amount）を顧客単位に合計し、合計した売上金額の外れ値を抽出せよ。ただし、顧客IDが"Z"から始まるのものは非会員を表すため、除外して計算すること。なお、ここでは外れ値を第一四分位と第三四分位の差であるIQRを用いて、「第一四分位数-1.5×IQR」よりも下回るもの、または「第三四分位数+1.5×IQR」を超えるものとする。結果は10件表示させれば良い。

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
FROM sales_amount
CROSS JOIN (
    SELECT PERCENTILE_CONT(0.25) WITHIN GROUP(ORDER BY sum_amount) as amount_25per,
           PERCENTILE_CONT(0.75) WITHIN GROUP(ORDER BY sum_amount) as amount_75per
    FROM sales_amount   
) stats_amount
WHERE sum_amount < amount_25per - (amount_75per - amount_25per) * 1.5 
    OR amount_75per + (amount_75per - amount_25per) * 1.5 < sum_amount
LIMIT 10;

問７９　

S-079: 商品テーブル（product）の各項目に対し、欠損数を確認せよ。

SELECT 
    SUM(CASE WHEN product_cd IS NULL THEN 1 ELSE 0 END) AS product_cd,
    SUM(CASE WHEN category_major_cd IS NULL THEN 1 ELSE 0 END) AS category_major_cd,
    SUM(CASE WHEN category_medium_cd IS NULL THEN 1 ELSE 0 END) AS category_medium_cd,
    SUM(CASE WHEN category_small_cd IS NULL THEN 1 ELSE 0 END) AS category_small_cd,
    SUM(CASE WHEN unit_price IS NULL THEN 1 ELSE 0 END) AS unit_price,
    SUM(CASE WHEN unit_cost IS NULL THEN 1 ELSE 0 END) AS unit_cost
FROM product LIMIT 10

問８０　

S-080: 商品テーブル（product）のいずれかの項目に欠損が発生しているレコードを全て削除した新たなproduct_1を作成せよ。なお、削除前後の件数を表示させ、前設問で確認した件数だけ減少していることも確認すること。

DROP TABLE IF EXISTS product_1;
CREATE TABLE product_1 AS (
    SELECT * FROM product
    WHERE unit_price IS NOT NULL OR unit_cost IS NOT NULL
);
SELECT COUNT(1) FROM product_1;