Curated SQL 70 - Easy #1

in Sql
Curated SQL 70 - Easy #1

LeetCode에서 제공하는 Curated-SQL-70 - Easy #1 입니다.

586. Customer Placing the Largest Number of Orders

SELECT customer_number
FROM (
    SELECT customer_number, COUNT(*) AS order_count
    FROM Orders
    GROUP BY customer_number
) OrderCnt
ORDER BY order_count DESC
LIMIT 1

607. Sales Person

SELECT name
FROM SalesPerson
WHERE sales_id NOT IN (
    SELECT a.sales_id
    FROM Orders a
    JOIN Company b ON a.com_id = b.com_id
    WHERE b.name = "RED"
)

1050. Actors and Directors Who Cooperated At Least Three Times

SELECT actor_id, director_id
FROM (
    SELECT actor_id, director_id, COUNT(*) AS count
    FROM ActorDirector
    GROUP BY actor_id, director_id
) meetCnt
WHERE count >= 3

1084. Sales Analysis III

SELECT p.product_id, p.product_name
FROM Product p, Sales s
WHERE p.product_id = s.product_id
GROUP BY s.product_id
HAVING MIN(s.sale_date) >= '2019-01-01' AND MAX(s.sale_date) <= '2019-03-31';

511. Game Play Analysis I

SELECT player_id, min(event_date) as first_login
FROM Activity
GROUP BY player_id

1141. User Activity for the Past 30 Days I

SELECT activity_date AS day, count(*) AS active_users
FROM (
    SELECT activity_date, count(*) AS active_count
    FROM Activity
    GROUP BY activity_date, user_id
) ActivityCount
GROUP BY activity_date
HAVING DATE_FORMAT(day,"%Y-%m-%d") BETWEEN "2019-06-28" AND "2019-07-27"

1148. Article Views I

SELECT author_id AS id
FROM Views
WHERE author_id = viewer_id
GROUP BY author_id
ORDER BY id ASC

1407. Top Travellers

SELECT a.name, IFNULL(b.travelled_distance, 0) AS travelled_distance
FROM Users a
LEFT JOIN (
    SELECT user_id, sum(distance) AS travelled_distance
    FROM Rides
    GROUP BY user_id
) b ON a.id = b.user_id
ORDER BY travelled_distance DESC, name ASC

610. Triangle Judgement

SELECT x, y, z,
    CASE WHEN x + y <= z OR x + z <= y OR y + z <= x THEN 'No'
    ELSE 'Yes' 
    END AS triangle
FROM Triangle

1068. Product Sales Analysis I

SELECT B.product_name, A.year, A.price
FROM Sales A
JOIN Product B ON A.product_id = B.product_id