スキンケアデータベースで練習問題(SQLのJOIN・GROUP BY・WITH句など)

こんばんは、Yuinaです🗺️

今日は、データベースの練習問題を作ってみました。

今回のブログでは、スキンケア大好きギャルたちの「レビュー」や「購入履歴」から、人気のアイテムや肌タイプ別の傾向をデータで見ていきます💄💕「綺麗な肌を目指す方たちのスキンケア記録」がデータになってたら、めっちゃ楽しそうじゃないですか〜？

加えて、自分の肌タイプに合う商品がデータでわかったら、最強にうれしくないですか？！

それでは、うるツヤ肌とSQLスキル、ダブルで手に入れちゃいましょうっ👑✨

環境: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)

練習問題
答え
まとめ

練習問題

問題1：カテゴリごとの商品の平均価格を求めよ（GROUP BY）

問題2：肌質ごとのユーザー数（WITH句 + GROUP BY）

問題3：一人あたりの平均購入金額を算出せよ（JOIN + GROUP BY）

問題4：課金率を求めよ（ゲームでいう課金率）

　定義：課金ユーザー数 ÷ 全ユーザー数（1回でも購入した人を課金ユーザーとみなす）

問題5：最もレビュー数が多い商品は？（JOIN + 集計）

問題6：30代で、最も使われているカテゴリ（JOIN + WHERE + GROUP）

答え

問題１：

SELECT 
	category, 
	AVG(price) 
FROM 
	users t1 
INNER JOIN purchase t2 
ON 
	t1.user_id = t2.user_id 
LEFT JOIN product t3
ON 
	t2.product_id = t3.product_id 
GROUP BY 
	category;

※ROUND(AVG(price),2)で小数点以下を四捨五入するとより良し！

問題２：

SELECT
  t1.name,
  AVG(t3.price) AS avg_price
FROM 
  users t1
INNER JOIN purchase t2
  ON t1.user_id = t2.user_id
INNER JOIN product t3
  ON t2.product_id = t3.product_id
GROUP BY
  t1.user_id, t1.name;

問題３：

SELECT
	AVG(price) 
FROM 
	users t1 
INNER JOIN 
	purchase t2 
ON 
	t1.user_id = t2.user_id 
LEFT JOIN 
	product t3 
ON 
	t2.product_id = t3.product_id;

users と purchase は INNER JOIN：「購入履歴があるユーザー」のみ対象。

purchase と product は LEFT JOIN：「購入履歴はあるけど、productに紐づいていない」ケースも含む。

※ROUND(AVG(price),2)で小数点以下を四捨五入するとより良し！

問題４：

WITH  
  paying_user AS (
    SELECT COUNT(DISTINCT user_id) AS paying 
    FROM purchase
  ),
  total_user AS (
    SELECT COUNT(*) AS total 
    FROM users
  )
SELECT   
  paying_user.paying * 1.0 / total_user.total AS paying_ratio 
FROM   
  paying_user, 
  total_user;

問題５：

SELECT 
  t1.name, 
  COUNT(t2.review_id) AS review
FROM 
  product t1 
LEFT OUTER JOIN reviews t2 
  ON t1.product_id = t2.product_id 
GROUP BY 
  t1.name 
ORDER BY 
  review DESC
LIMIT 1;

LEFT OUTER JOIN :「レビューが1件もない商品」も集計対象に含みたい。

問題６：

SELECT
	t3.category,
	COUNT(*) AS category_count 
FROM   
	users t1
INNER JOIN 
	purchase t2 
ON 
	t1.user_id = t2.user_id
INNER JOIN 
	product t3 
ON 
	t2.product_id = t3.product_id 
WHERE   
	t1.age BETWEEN 30 AND 39 
GROUP BY   
	t3.category 
ORDER BY   
	category_count DESC
LIMIT 2;