SQL初心者〜中級者のための練習問題&解答例3

第3弾を作ってみました。
SQL力向上のためにお役立てください。

その1: [SQL初心者〜中級者のための練習問題&解答例1](https://qiita.com/_hiro_dev/items/ece39759879c5d1f8536 "SQL初心者〜中級者のための練習問題&解答例1")
その2: [SQL初心者〜中級者のための練習問題&解答例2](https://qiita.com/_hiro_dev/items/e16fa143487d2c3686c9 "SQL初心者〜中級者のための練習問題&解答例2")

# ツールの紹介
[DB Fiddle](https://www.db-fiddle.com/ "DB Fiddle")を使うとブラウザ上で簡単にSQLを実行できます。
画面左側のSchema SQLにスキーマを、画面右側のQuery SQLにクエリ（SELECT句）を書いて、画面上部の「Run」ボタンを押すだけです。
環境構築要らないので便利です。

# 題材
**ユーザーテーブル**

| ユーザーID | ユーザー名 | メールアドレス     | 年齢 | 
| ---------- | ---------- | ------------------ | ---- | 
| 1          | もっくん   | mokkun@example.com | 19   | 
| 2          | みみこ     | mimiko@example.net | 20   | 
| 3          | さくら     | sakura@example.com | 31   | 
| 4          | ひよこ     | hiyoko@example1.jp | 23   | 
| 5          | すずき     | suzuki@example.jp  | 28   | 

**フォローテーブル**

※フォロワー:フォローする側、フォロイー:フォローされる側

| フォロワーID | フォロイーID | 
| ------------ | ------------ | 
| 1            | 2            | 
| 1            | 3            | 
| 1            | 4            | 
| 1            | 5            | 
| 3            | 1            | 
| 3            | 2            | 
| 4            | 5            | 
| 5            | 1            | 
| 5            | 2            | 
| 5            | 3            | 
| 5            | 4            | 

<details><summary>Schema SQL</summary><div>

```sql
CREATE TABLE `users` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT PRIMARY KEY,
  `name` varchar(255) NOT NULL,
  `email` varchar(255) NOT NULL,
  `age` int(3) unsigned NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO `users` (`id`, `name`, `email`, `age`)
VALUES
	(1, 'もっくん', 'mokkun@example.com', 19),
	(2, 'みみこ', 'mimiko@example.net', 20),
	(3, 'さくら', 'sakura@example.com', 31),
	(4, 'ひよこ', 'hiyoko@example1.jp', 23),
	(5, 'すずき', 'suzuki@example.jp', 28);

CREATE TABLE `follows` (
  `follower_id` int(11) unsigned NOT NULL,
  `followee_id` int(11) unsigned NOT NULL,
  PRIMARY KEY(`follower_id`, `followee_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO `follows` (`follower_id`, `followee_id`)
VALUES
	(1, 2),
	(1, 3),
	(1, 4),
	(1, 5),
	(3, 1),
	(3, 2),
	(4, 5),
	(5, 1),
	(5, 2),
	(5, 3),
	(5, 4);
```
</div></details>

# 問1
さくらがフォローしているユーザーの名前を一覧で表示せよ。

| name |
| ---- |
| もっくん |
| みみこ  |

<details><summary>解答例</summary><div>

```sql
SELECT
	u2.name
FROM
	users u1
INNER JOIN follows f
	ON u1.id = f.follower_id
INNER JOIN users u2
	ON f.followee_id = u2.id
WHERE
	u1.id = 3
```
</div></details>

# 問2
誰もフォローしていないユーザーの名前を表示せよ。

| name |
| ---- |
| みみこ  |

<details><summary>解答例</summary><div>

```sql
SELECT
	u.name
FROM
	users u
LEFT JOIN follows f
	ON u.id = f.follower_id
WHERE
	f.follower_id IS NULL
```
</div></details>

# 問3
10代、20代、30代といった年代別にフォロー数の平均を表示せよ。

| age_group | avg_count |
| --------- | --------- |
| 10代       | 4.0000    |
| 20代       | 1.6667    |
| 30代       | 2.0000    |

<details><summary>解答例1</summary><div>

```sql
SELECT
	CONCAT((FLOOR(age / 10) * 10), '代') AS age_group,
	AVG(count) AS avg_count
FROM (
	SELECT
		u.age,
		SUM(CASE WHEN f.follower_id IS NOT NULL THEN 1 ELSE 0 END) AS count
	FROM
		users u
	LEFT JOIN follows f
		ON u.id = f.follower_id
	GROUP BY
		u.id
) follows_count
GROUP BY
	CONCAT((FLOOR(age / 10) * 10), '代')
```
</div></details>

<details><summary>解答例2（提供: @takahasinaoki さん）</summary><div>

```sql
SELECT
    CONCAT(age_group * 10, '代') AS age_group,
    AVG(count) AS avg_count
FROM (
    SELECT
        FLOOR(age / 10) AS age_group,
        count(f.follower_id) AS count
    FROM
        users u
    LEFT JOIN follows f
        ON u.id = f.follower_id
    GROUP BY
        u.id
) follows_count
GROUP BY
    age_group
```
</div></details>

<details><summary>解答例3（提供: @kensei_o さん）</summary><div>

```sql
SELECT
    CONCAT((FLOOR(age / 10) * 10), '代') AS age_group,
    SUM(CASE WHEN f.follower_id THEN 1 ELSE 0 END) / COUNT(DISTINCT u.id) AS avg_count
FROM
    users u
LEFT JOIN follows f
    ON  u.id = f.follower_id
GROUP BY
    CONCAT((FLOOR(age / 10) * 10), '代')
```
</div></details>

# 問4
相互フォローしているユーザーのIDを表示せよ。
なお、重複は許さないものとする。

| id1 | id2 |
| --- | --- |
| 1   | 3   |
| 1   | 5   |
| 4   | 5   |

<details><summary>解答例1</summary><div>

```sql
SELECT
	CASE WHEN f1.follower_id > f1.followee_id THEN f1.followee_id ELSE f1.follower_id END AS id1,
	CASE WHEN f1.follower_id > f1.followee_id THEN f1.follower_id ELSE f1.followee_id END AS id2
FROM
	follows f1
INNER JOIN follows f2
	ON f1.follower_id = f2.followee_id
	AND f1.followee_id = f2.follower_id
GROUP BY
	CASE WHEN f1.follower_id > f1.followee_id THEN f1.followee_id ELSE f1.follower_id END,
	CASE WHEN f1.follower_id > f1.followee_id THEN f1.follower_id ELSE f1.followee_id END
```
</div></details>

<details><summary>解答例2（提供: @takahasinaoki さん）</summary><div>

```sql
SELECT 
	f1.follower_id As id1,
	f1.followee_id As id2
FROM
	follows f1
INNER JOIN follows f2
	ON f1.follower_id = f2.followee_id
	AND f1.followee_id = f2.follower_id
WHERE
	f1.follower_id < f1.followee_id
```
</div></details>

<details><summary>解答例3（提供: @HIKARU_JY さん）</summary><div>

```sql
SELECT
	*
FROM
	follows f1
WHERE
	EXISTS (
		SELECT
			*
		FROM
			follows f2
		WHERE
			f1.follower_id = f2.followee_id
			AND f2.follower_id = f1.followee_id
	)
	AND f1.follower_id < f1.followee_id
```
</div></details>