240822(+78) | SQL 코드카타 | 99~106 | leetcode

Q.99 LEETCODE 2356

2356. Number of Unique Subjects
      a solution to calculate the number of unique subjects each teacher teaches in the university.

SELECT teacher_id
    ,count(distinct subject_id) as  cnt
FROM Teacher
GROUP BY teacher_id

Q.100 LEETCODE 1141

1141. User Activity for the Past 30 Days I
      a solution to find the daily active user count for a period of 30 days ending 2019-07-27 inclusively. A user was active on someday if they made at least one activity on that day.

SELECT date_format(activity_date, '%Y-%m-%d') as day
     , count(distinct user_id) as active_users
FROM Activity
WHERE date(activity_date) between '2019-06-28' and '2019-07-28'
GROUP BY day 

Q.101 LEETCODE 1070

1070. Product Sales Analysis III
      solution to select the product id, year, quantity, and price for the first year of every product sold.

SELECT product_id, year as first_year, quantity, price 
FROM sales
WHERE (product_id,year) in
(
    SELECT product_id ,min(year) as year 
    FROM sales 
    GROUP BY product_id
)

• first_year를 필터링하기 위해 cte에 min(year)을 사용

Q.102 LEETCODE 596

596. Classes More Than 5 Students
     solution to find all the classes that have at least five students.

SELECT class
FROM Courses
GROUP BY class
HAVING count(student) >=5

Q.103 LEETCODE 1729

1729. Find Followers Count
      solution that will, for each user, return the number of followers.

SELECT user_id
    , count(follower_id) as followers_count
FROM Followers
GROUP BY user_id
ORDER BY user_id 

Q.104 LEETCODE 619

619. Biggest Single Number
     A single number is a number that appeared only once in the MyNumbers table.
     Find the largest single number. If there is no single number, report null.

SELECT max(num) as num
FROM mynumbers
WHERE num in (
    SELECT num 
    FROM mynumbers
    GROUP BY num
    HAVING COUNT(num) =1
)

Q.105 LEETCODE 1045

1045. Customers Who Bought All Products
      a solution to report the customer ids from the Customer table that bought all the products in the Product table.

SELECT customer_id 
FROM Customer   
GROUP BY customer_id  
HAVING count(distinct product_key)  = (select count(*) from Product);

Q.106 LEETCODE 1731

1731. The Number of Employees Which Report to Each Employee
      For this problem, we will consider a manager an employee who has at least 1 other employee reporting to them.
      Write a solution to report the ids and the names of all managers, the number of employees who report directly to them, and the average age of the reports rounded to the nearest integer.
      Return the result table ordered by employee_id.
      

SELECT e2.employee_id
     , e2.name
     , count(e1.reports_to) as reports_count
     , round(avg(e1.age),0) as average_age
FROM employees e1 join employees e2
ON e1.reports_to = e2.employee_id
GROUP BY employee_id
ORDER BY employee_id