スキンケアデータベースで練習問題②(GROUP BY)

IT

こんにちは。Yuinaです☀️

今回は、前回に引き続きスキンケアデータベースでスキンケア大好きギャルたちの「レビュー」や「購入履歴」から、いろいろな角度でをデータで見ていきます💄💕

よろしくお願いいたします！

環境はこちら↓

環境:MySQL / SQLite / PostgreSQL（構文を少し調整すればOK）
テーマ:👇✨

このプロジェクトは、スキンケアに関するユーザーの行動・商品レビュー・購入傾向などを分析できるように設計された練習用のデータベースです。
データ分析やSQLの練習にピッタリなように、「肌タイプ」「商品カテゴリ」「レビュー」「継続購入者」などを用意しました。

テーブル名内容
usersスキンケアを使っているユーザーのプロフィール情報（年齢、肌タイプなど）
products商品の情報（カテゴリや価格など）
purchasesユーザーがいつどの商品を買ったかの履歴
reviews商品の評価（5段階）やコメントなどのレビュー

users:

user_id,name,age,skin_type
1,ゆいぴ,33,sensitive
2,まいち,31,combination
3,さきぽよ,28,oily
4,なつたん,33,combination
5,えりちょ,18,sensitive
6,りんご姫,25,dry
7,あやまる,30,sensitive
8,ちなてぃ,20,sensitive
9,ももにゃん,27,oily
10,かれんぴ,30,sensitive

product:

product_id,name,category,price
1,うるぷる化粧水,toner,3223
2,てかりブロック乳液,moisturizer,2316
3,ガチ守る日焼け止め,sunscreen,1396
4,ふわふわ洗顔フォーム,cleanser,2947
5,モチモチ美容液,serum,3008
6,毛穴バイバイパック,mask,1632
7,透き通る化粧水,toner,1336
8,水光肌ジェル,moisturizer,1567
9,バリアサポートUV,sunscreen,3047
10,ぷるんリップ美容液,serum,1752

purchase:

purchase_id,user_id,product_id,purchase_date
1,1,5,2025-02-02
2,1,10,2025-02-28
3,1,9,2025-03-24
4,2,8,2025-04-20
5,2,4,2025-01-05
6,2,2,2025-03-28
7,2,7,2025-03-24
8,3,9,2025-03-27
9,3,3,2025-03-01
10,3,3,2025-03-07

reviews:

review_id,user_id,product_id,rating,comment
1,10,8,2,乾燥しにくくなった！
2,4,9,5,ヒリヒリしないの最高🥹
3,3,4,2,ヒリヒリしないの最高🥹
4,6,5,1,マジ神！肌変わった🥺
5,5,1,1,ヒリヒリしないの最高🥹
6,10,4,3,ヒリヒリしないの最高🥹
7,5,6,4,ヒリヒリしないの最高🥹
8,2,6,4,香りがエモい！
9,3,4,4,うーん、ちょっとベタつくかも
10,10,7,5,友達にもおすすめしたい💕

全テーブルとカラム：

mysql> show tables;
+---------------------+
| Tables_in_skin_care |
+---------------------+
| product             |
| purchase            |
| reviews             |
| users               |
+---------------------+
4 rows in set (0.02 sec)

mysql> desc product;
+------------+------+------+-----+---------+-------+
| Field      | Type | Null | Key | Default | Extra |
+------------+------+------+-----+---------+-------+
| product_id | int  | YES  |     | NULL    |       |
| name       | text | YES  |     | NULL    |       |
| category   | text | YES  |     | NULL    |       |
| pri      | int  | YES  |     | NULL    |       |
+------------+------+------+-----+---------+-------+
4 rows in set (0.01 sec)

mysql> desc purchase;
+---------------+------+------+-----+---------+-------+
| Field         | Type | Null | Key | Default | Extra |
+---------------+------+------+-----+---------+-------+
| purchase_id   | int  | YES  |     | NULL    |       |
| user_id       | int  | YES  |     | NULL    |       |
| product_id    | int  | YES  |     | NULL    |       |
| purchase_date | date | YES  |     | NULL    |       |
+---------------+------+------+-----+---------+-------+
4 rows in set (0.01 sec)

mysql> desc reviews;
+------------+------+------+-----+---------+-------+
| Field      | Type | Null | Key | Default | Extra |
+------------+------+------+-----+---------+-------+
| review_id  | int  | YES  |     | NULL    |       |
| user_id    | int  | YES  |     | NULL    |       |
| product_id | int  | YES  |     | NULL    |       |
| rating     | int  | YES  |     | NULL    |       |
| comment    | text | YES  |     | NULL    |       |
+------------+------+------+-----+---------+-------+
5 rows in set (0.01 sec)

mysql> desc users;
+-----------+------+------+-----+---------+-------+
| Field     | Type | Null | Key | Default | Extra |
+-----------+------+------+-----+---------+-------+
| user_id   | int  | NO   |     | NULL    |       |
| name      | text | NO   |     | NULL    |       |
| age       | int  | NO   |     | NULL    |       |
| skin_type | text | NO   |     | NULL    |       |
+-----------+------+------+-----+---------+-------+
4 rows in set (0.00 sec)

練習問題

問題7：3ヶ月以上にわたり、月に1回以上購入しているユーザーを抽出してください

問題8：初回購入日から90日以内に3回以上購入したユーザーを抽出してください

問題9：スキンケアカテゴリを3回以上買っているユーザーを抽出してください

答えと解説

問題7:

-- 購入しているユーザー

WITH purchase_mon AS (
    SELECT
        user_id,
        DATE_FORMAT(purchase_date, '%Y-%m') AS purchase_month  
    FROM
        purchase
    GROUP BY
        user_id,
        DATE_FORMAT(purchase_date, '%Y-%m')
),

-- 購入しているユーザーの購入月数をカウント
purchase_count AS (
    SELECT 
        user_id,
        COUNT(*) AS month_count
    FROM
        purchase_mon 
    GROUP BY 
        user_id
)

-- 集計：3ヶ月以上購入しているユーザーを抽出
SELECT
    user_id
FROM 
    purchase_count
WHERE
    month_count >= 3;

問題8:

-- 初回購入データの抽出
WITH user_first_purchase AS (
  SELECT 
    user_id, 
    MIN(purchase_date) AS first_date
  FROM 
    purchase
  GROUP BY 
    user_id
)

-- 集計
SELECT 
  p.user_id
FROM 
  purchase p
JOIN 
  user_first_purchase f ON p.user_id = f.user_id
WHERE 
  p.purchase_date <= DATE_ADD(f.first_date, INTERVAL 90 DAY) -- 条件：初回購入から90日以内
GROUP BY 
  p.user_id
HAVING 
  COUNT(*) >= 3; -- 3回以上購入

ちなみに他のカラムと一緒に出してみると以下のようになります。


-- 初回購入日を取得
WITH user_first_purchase AS (
  SELECT 
    user_id, 
    MIN(purchase_date) AS first_date
  FROM 
    purchase
  GROUP BY 
    user_id
),
-- 初回から90日以内の購入だけを抽出
purchase_within_90days AS (
  SELECT 
    p.*
  FROM 
    purchase p
  JOIN 
    user_first_purchase f ON p.user_id = f.user_id
  WHERE 
    p.purchase_date <= DATE_ADD(f.first_date, INTERVAL 90 DAY)
),
-- その中で3回以上購入したユーザーを抽出
frequent_buyers AS (
  SELECT 
    user_id
  FROM 
    purchase_within_90days
  GROUP BY 
    user_id
  HAVING 
    COUNT(*) >= 3
)

-- 最終表示：ユーザー情報＋購入商品情報（直近90日以内）✨
SELECT 
  u.user_id,
  u.name AS user_name,
  u.age,
  u.skin_type,
  pr.name AS product_name,
  pr.category,
  pr.price,
  pw.purchase_date
FROM 
  purchase_within_90days pw
JOIN 
  users u ON pw.user_id = u.user_id
JOIN 
  product pr ON pw.product_id = pr.product_id
JOIN 
  frequent_buyers fb ON pw.user_id = fb.user_id
ORDER BY 
  u.user_id, pw.purchase_date;

問題9:

SELECT 
  pu.user_id
FROM 
  purchase pu
JOIN 
  product pr ON pu.product_id = pr.product_id
WHERE 
  pr.category = 'toner'
GROUP BY 
  pu.user_id
HAVING 
  COUNT(*) >= 3;

まとめ

いかがでしたか？前回より少し難しかったのではないでしょうか！

データベースってむずそう…って思っていましたが、スキンケアみたいに身近なテーマでやるとわかりやすいし、ちょっと楽しくなります🥺💄💖

これからも、推し肌タイプ別おすすめプロダクトとか、スキンケアの沼にハマる女子を応援できるデータ分析してこ〜って思いました✌️🌸

みんなもSQLで肌ケアマスターになっちゃいましょう〜🧴✨

コメント

タイトルとURLをコピーしました