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

問２１　

S-021: レシート明細データ（receipt）に対し、件数をカウントせよ。

SELECT COUNT(*) FROM receipt

問２２　

S-022:レシート明細テーブル（receipt）の顧客ID（customer_id）に対し、
ユニーク件数をカウントせよ。

SELECT COUNT(DISTINCT customer_id) FROM receipt;

問２３　

S-023:レシート明細テーブル（receipt）に対し、店舗コード（store_cd）ごとに
売上金額（amount）と売上数量（quantity）を合計せよ。

SELECT store_cd, SUM(amount) AS sum_amount, SUM(quantity) AS sum_quantity
FROM receipt GROUP BY store_cd;

問２４　

S-024: レシート明細データ（receipt）に対し、顧客ID（customer_id）ごとに最も新しい売上年月日（sales_ymd）を求め、10件表示せよ。

SELECT customer_id,　MAX(sales_ymd) AS max_sales_ymd
FROM receipt
GRROUP BY customer_id
LIMIT 10;

問２５　

S-025:レシート明細テーブル（receipt）に対し、顧客ID（customer_id）ごとに
最も古い売上日（sales_ymd）を求め、10件表示せよ。

SELECT customer_id, MIN(sales_ymd) AS min_sales_ymd
FROM receipt
GROUP BY customer_id
LIMIT 10;

問２６　

S-026:レシート明細テーブル（receipt）に対し、顧客ID（customer_id）ごとに
最も新しい売上日（sales_ymd）と古い売上日を求め、両者が異なるデータを10件表示せよ。

SELECT customer_id, MAX(sales_ymd) AS max_sales_ymd, 
MIN(sales_ymd) as min_sales_ymd 
FROM receipt GROUP BY customer_id
HAVING MAX(sales_ymd) != MIN(sales_ymd)
LIMIT 10;

問２７　

S-027:レシート明細テーブル（receipt）に対し、店舗コード（store_cd）ごとに
売上金額（amount）の平均を計算し、降順でTOP5を表示せよ。

SELECT store_cd, AVG(amount) AS avg_amount
FROM receipt GROUP BY store_cd
ORDER BY avg_amount DESC 
LIMIT 5;

問２８　

S-028:レシート明細テーブル（receipt）に対し、店舗コード（store_cd）ごとに
売上金額（amount）の中央値を計算し、降順でTOP5を表示せよ。

SELECT store_cd,
PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY amount) as median
FROM receipt
GROU BY store_cd
ORDER BY median desc
LIMIT 5;

問２９　

S-029:レシート明細テーブル（receipt）に対し、店舗コード（store_cd）ごとに
商品コードの最頻値を求めよ。

SELECT store_cd,
MODE() WITHIN GROUP (ORDER BY product_cd)
FROM receipt
GROUP BY store_cd;

問３０　

S-030:レシート明細テーブル（receipt）に対し、店舗コード（store_cd）ごとに
売上金額（amount）の標本分散を計算し、降順にTOP5を表示せよ。

SELECT store_cd,
VAR_SAMP(amount) AS var_amount
FROM receipt
GROUP BY store_cd
ORDER BY var_amount DESC
LIMIT 5;

問３１　

S-031:レシート明細テーブル（receipt）に対し、店舗コード（store_cd）ごとに
売上金額（amount）の標本標準偏差を計算し、降順にTOP5を表示せよ。

SELECT store_cd,
STDDEV_SAMP(amount) AS std_amount
FROM receipt
GROUP BY store_cd
ORDER BY std_amount DESC
LIMIT 5;

問３２　

S-032:レシート明細テーブル（receipt）に対し、
売上金額（amount）について25％刻みでパーセンタイル値を求めよ。

SELSECT PERCENTILE_CONT(0.25) WITHIN GROUP (ORDER BY amount) AS per_25,
PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY amount) AS per_50,
PERCENTILE_CONT(0.75) WITHIN GROUP (order by amount) AS per_75,
PERCENTILE_CONT(1.0) WITHIN GROUP (order by amount) AS per_100
FROM receipt;

問３３　

S-033:レシート明細テーブル（receipt）に対し、店舗コード（store_cd）ごとに
売上金額（amount）の平均を計算し、330以上のものを抽出せよ。

SELECT store_cd,
AVG(amount) AS avg_amount
FROM receipt
GROUP BY store_cd
HAVING AVG(amount) >= 330;

問３４　

S-034:レシート明細テーブル（receipt）に対し、顧客ID（customer_id）ごとに
売上金額（amount）を合計して全顧客の平均を求めよ。ただし、顧客IDが"Z"から始まるのものは非会員を表すため、除外して計算すること。

WITH customer_amount AS (
                          SELECT customer_id,
                                 SUM(amount) AS sum_amount
                          FROM receipt
                          WHERE customer_id NOT LIKE 'Z%'
                          GROUP BY customer_id
                         )
SELECT AVG(sum_amount)
FROM customer_amount;

問３５　

S-035:レシート明細テーブル（receipt）に対し、顧客ID（customer_id）ごとに販売金額（amount）を合計して全顧客の平均を求め、平均以上に買い物をしている顧客を抽出せよ。
ただし、顧客IDが"Z"から始まるのものは非会員を表すため、除外して計算すること。
なお、データは10件だけ表示させれば良い。

WITH customer_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 customer_amount
WHERE sum_amount >= (SELECT AVG(sum_amount) FROM customer_amount)
LIMIT 10;

問３６　

S-036:レシート明細テーブル（receipt）と店舗テーブル（store）を内部結合し、
レシート明細テーブルの全項目と店舗テーブルの店舗名(store_name)を10件表示せよ。

SELECT r.*, s.store_name
FROM receipt r
JOIN store s
ON r.store_cd = s.store_cd
LIMIT 10;

問３７　

S-037: 商品テーブル（product）とカテゴリテーブル（category）を内部結合し、商品テーブルの全項目とカテゴリテーブルの小区分名（category_small_name）を10件表示させよ。

SELECT p.*, c.category_small_name
FROM product p
JOIN category c
ON p.category_small_cd = c.category_small_cd
LIMIT 10;

問３８　

S-038: 顧客テーブル（customer）とレシート明細テーブル（receipt）から、各顧客ごとの売上金額合計を求めよ。ただし、買い物の実績がない顧客については売上金額を0として表示させること。また、顧客は性別コード（gender_cd）が女性（1）であるものを対象とし、非会員（顧客IDが'Z'から始まるもの）は除外すること。なお、結果は10件だけ表示させれば良い。

WITH customer_amount AS (
    SELECT customer_id, SUM(amount) AS sum_amount
    FROM receipt
    GROUP BY customer_id
)
SELECT c.customer_id, COALESCE(a.sum_amount,0)
FROM customer c
LEFT JOIN customer_amount a
ON c.customer_id = a.customer_id
WHERE c.gender_cd = '1'
      and c.customer_id not like 'Z%'
LIMIT 10;

問３９　

S-039: レシート明細テーブル（receipt）から売上日数の多い顧客の上位20件と、売上金額合計の多い顧客の上位20件を抽出し、完全外部結合せよ。ただし、非会員（顧客IDが'Z'から始まるもの）は除外すること。

WITH customer_days AS (
    select customer_id, COUNT(DISTINCT sales_ymd) come_days
    FROM receipt
    WHERE customer_id NOT LIKE 'Z%'
    GROUP BY customer_id
    ORDER BY come_days DESC LIMIT 20
),
customer_amount AS (
    SELECT customer_id, SUM(amount) buy_amount
    FROM receipt 
    WHERE customer_id NOT LIKE 'Z%'
    GROUP BY customer_id
    ORDER BY buy_amount DESC LIMIT 20
)
SELECT COALESCE(d.customer_id, a.customer_id), d.come_days, a.buy_amount
FROM customer_days d
FULL JOIN customer_amount a
ON d.customer_id = a.customer_id;

問４０　

S-040: 全ての店舗と全ての商品を組み合わせると何件のデータとなるか調査したい。店舗（store）と商品（product）を直積した件数を計算せよ。

SELECT COUNT(1) FROM store CROSS JOIN product;