Leet Code SQL50
1148. Article Views I
解法1
group by
select author_id as id from Views where author_id = viewer_id
group by author_id
order by author_id;
解法2
distinct
select distinct author_id as id from Views where author_id = viewer_id
order by author_id;
1378. Replace Employee ID With The Unique Identifier
解法1
select eu.unique_id, e.name from Employees e left join EmployeeUNI
eu on e.id = eu.id;
解法2
SELECT EmployeeUNI.unique_id, Employees.name
FROM Employees NATURAL LEFT JOIN EmployeeUNI;
Here's an alternative to the LEFT JOIN solution that doesn't use the ON clause or the USING clause in the JOIN statement. Since the column that is being joined has the same name, the NATURAL LEFT JOIN can be used. The NATURAL [LEFT] JOIN of two tables is defined to be semantically equivalent to an INNER JOIN or a LEFT JOIN with a USING clause that names all columns that exist in both tables.
→NATURAL LEFT JOIN知らなかった。
1581. Customer Who Visited but Did Not Make Any Transactions
-- Query 1
SELECT customer_id, COUNT(visits.visit_id) AS count_no_trans
FROM visits
LEFT JOIN transactions USING(visit_id)
WHERE transactions.transaction_id IS NULL
GROUP BY 1;
-- Query 2
SELECT v.customer_id, COUNT(v.customer_id) AS count_no_trans
FROM Visits v
WHERE visit_id NOT IN (
SELECT visit_id FROM Transactions
)
GROUP BY v.customer_id;
一般的に、Query 1(LEFT JOINを使用するもの)の方が、Query 2(NOT INを使用するもの)よりもパフォーマンスが優れていると考えられます。理由は以下の通りです:
インデックスの利用:
Query 1は、JOINを使用しているため、visit_idのインデックスを効果的に利用できる可能性が高いです。
Query 2のNOT IN句は、サブクエリ内のインデックスを効果的に使用できない場合があります。
2. 実行計画:
LEFT JOINは、データベースエンジンが最適化しやすい操作です。
NOT IN句を含むサブクエリは、最適化が難しく、全テーブルスキャンを引き起こす可能性があります。
3. スケーラビリティ:
Query 1は、大規模なデータセットでもよく機能します。
Query 2は、Transactionsテーブルが大きい場合、パフォーマンスが低下する可能性があります。
NULL値の扱い:
Query 1は、NULL値を明示的に処理しています。
Query 2のNOT IN句は、NULLの扱いに注意が必要で、予期しない結果を生む可能性があります。
ただし、実際のパフォーマンスは以下の要因に大きく依存します:
データの分布
インデックスの存在
テーブルのサイズ
データベースエンジンの特性
最終的な判断には、実際のデータセットで両方のクエリを実行し、実行計画とパフォーマンスを比較することをお勧めします。
また、パフォーマンスをさらに向上させるための第3の選択肢として、EXISTS句を使用する方法もあります:
SELECT v.customer_id, COUNT(*) AS count_no_trans
FROM Visits v
WHERE NOT EXISTS (
SELECT 1
FROM Transactions t
WHERE t.visit_id = v.visit_id
)
GROUP BY v.customer_id;
この方法は、多くの場合、NOT IN句よりも効率的で、LEFT JOINと同等かそれ以上のパフォーマンスを発揮することがあります。
577. Employee Bonus
Solution1
SELEXT e.name, b.bonus from Employee e
LEFT JOIN Bonus b on b.empId = e.empId
WHERE b.bonus < 1000 or b.bonus is null
Solution2
SELEXT e.name, b.bonus from Employee e
LEFT JOIN Bonus b on b.empId = e.empId
WHERE IFNULL(b.bonus, 0) < 1000;
こちらの方が少し早い。挙動の違いを見てみたい。
1280. Students and Examinations
むずかった、Cross JOINを知らなかった。
- CROSS JOIN
- GROUP BY 1,2,3という指定方法→可読性低いから微妙そう
SELECT s.student_id, s.student_name, sb.subject_name, count(e.student_id) as attended_exams
FROM Students s
CROSS JOIN Subjects sb
LEFT JOIN Examinations e ON e.student_id = s.student_id AND sb.subject_name = e.subject_name
group by 1,2,3
order by s.student_id, sb.subject_name
570. Managers with at Least 5 Direct Reports
SELECT name
FROM Employee
WHERE id IN
(SELECT managerId
FROM Employee
GROUP BY
managerId
HAVING
COUNT(id) >= 5);
サブクエリを使用しています(相関サブクエリ)
SELECT E1.name
FROM Employee E1
LEFT JOIN Employee E2 on E1.id = E2.managerId
GROUP BY E1.id
HAVING count(E1.id) >= 5
自己結合(self-join)を使用しています。
一般的に、Query 2の方がパフォーマンスが良い傾向にあります。これは、多くのデータベースエンジンが結合操作を効率的に最適化できるためです。
しかし、実際のパフォーマンスはデータの分布やインデックスの存在に大きく依存します。
Query 2の方が、追加の条件や列を加えるのが容易で、より柔軟性があります。
1934. Confirmation Rate
Medium①IFの使い方知らなかった。Postgresqlでも使えるのか?
②自分でやった時はCOUNT.. / COUNT .. にしてしまっていて、AVGでいいやん!と納得
SELECT
s.user_id,
ROUND(AVG(IF(c.action="confirmed",1,0)),2) AS confirmation_rate
FROM Signups s
LEFT JOIN Confirmations c on c.user_id = s.user_id
GROUP BY s.user_id
自分の実装(Submit通らず
SELECT
s.user_id,
ifnull(count(case when c.action = 'confirmed' then 1 end) / count(c.user_id),0) as confirmation_rate
FROM Signups s
LEFT JOIN Confirmations c on c.user_id = s.user_id
GROUP BY s.user_id
1633. Percentage of Users Attended a Contest
select count(user_id) from Users
で毎回その時のUserの数が取れるという発想を思い浮かばなかった。
SELECT
contest_id,
ROUND(count(user_id) * 100 / (select count(user_id) from Users) , 2) as percentage
FROM Register
GROUP BY contest_id
ORDER BY percentage desc, contest_id
1193. Monthly Transactions I
MediumMedium 単純に楽しかった。
Point1: monthの取得方法。
他の回答者のやり方としては切り取る方法があった.
ここは単純にどちらがパフォーマンスがいいのか見てみたい。
LEFT(trans_date, 7) AS month,
SUBSTR(trans_date, 1, 7) AS month,
Point2: count方法
これは面白い発想。= の結果が1になるからそれを使ってるっぽい?
SUM(state = 'approved') AS approved_count,
SUM((state = 'approved') * amount) AS approved_total_amount
# Write your MySQL query statement below
SELECT
DATE_FORMAT(trans_date, '%Y-%m') as month,
country,
COUNT(id) as trans_count,
SUM(CASE WHEN state = 'approved' THEN 1 ELSE 0 END) as approved_count,
SUM(amount) as trans_total_amount,
SUM(CASE WHEN state = 'approved' THEN amount ELSE 0 END) as approved_total_amount
FROM Transactions
GROUP BY month, country
550. Game Play Analysis IV
Medium初回ログインと次の日、2レコードを一度に扱う(横並びに扱う)感じが難しい
Select * From Activity
Where (player_id, event_date) in (
select player_id, min(event_date)
FROM Activity
Group BY player_id
初回ログインは取れるけどどうやって並べるか
またこれを忘れてた
Select
ROUND(COUNT(player_id) / (SELECT COUNT(Distinct player_id) FROM Activity), 2) as fraction
From Activity
Where (player_id, DATE_SUB(event_date, INTERVAL 1 DAY) ) in (
select player_id, min(event_date)
FROM Activity
Group BY player_id
)
Fractinの計算の場合、分母側はほぼいけるから分子を特定することに集中すればいけそう。
Sorting and Grouping
2356. Number of Unique Subjects Taught by Each Teacher
Point: Distinctを扱いきれていなかった
My Answer
Select
teacher_id,
COUNT(teacher_id) as cnt
FROM(
select teacher_id,subject_id
from Teacher
group by teacher_id, subject_id
) src
GROUP BY teacher_id
SELECT teacher_id, COUNT(DISTINCT subject_id) as cnt
FROM Teacher
GROUP BY teacher_id;
1141. User Activity for the Past 30 Days I
できた
SELECT activity_date as day, COUNT(DISTINCT user_id) as active_users
FROM Activity
WHERE activity_date between DATE_SUB('2019-07-28', INTERVAL 1 MONTH) AND '2019-07-28'
GROUP BY activity_date
1070. Product Sales Analysis III
こうしてしまった。WITH句を試せたといえば試せたけど無駄に長くなった感
WIth
s1 as (Select * from Sales),
s2 as (
SELECT
s.product_id, min(s.year) as year
FROM Sales s
GROUP BY product_id
)
SELECT
s1.product_id,
s1.year as first_year,
s1.quantity,
s1.price
FROM s1
JOIN s2
WHERE
s1.product_id = s2.product_id
AND s1.year = s2.year
最初この時方やったはずなんだが、なぜかできておらず
select product_id,year as first_year, quantity, price
from Sales
where(product_id, year) in (select product_id, min(year) from Sales group by product_id)
1729. Find Followers Count
pretty easy
SELECT
user_id,
COUNT(follower_id) as followers_count
FROM Followers
GROUP BY user_id
ORDER BY user_id
619. Biggest Single Number
My Solution→BEATS 79.23%なのでいいかな
SELECT
CASE WHEN COUNT(num) = 1 THEN num
ELSE null END AS num
FROM MyNumbers
GROUP BY num
ORDER BY COUNT(num), num desc
LIMIT 1
他の人はこういうのが多かった
SELECT MAX(num) AS num
FROM (
SELECT num
FROM MyNumbers
GROUP BY num
HAVING COUNT(num) = 1
) AS unique_numbers;
1045. Customers Who Bought All Products
Test Caseを通ったアンサー
SELECT customer_id
FROM(
SELECT
customer_id,product_key
FROM Customer
GROUP BY customer_id,product_key
) C1
GROUP BY customer_id
HAVING COUNT(customer_id) >= (
SELECT COUNT(*) FROM Product
)
通らなかったアンサー
SELECT
customer_id,product_key
FROM Customer
GROUP BY customer_id,product_key
HAVING COUNT(product_key) >= (
SELECT COUNT(*) FROM Product
)
DISTINCTがまだ使えてない。。
これでいけてたかーーー
SELECT c.customer_id
FROM Customer c
GROUP BY c.customer_id
HAVING COUNT(DISTINCT c.product_key) = (SELECT COUNT(DISTINCT product_key) FROM Product);