[SQL] LeetCode Medium Database Problem (11~22)

11. Activity Participant

SELECT activity
FROM Friends
GROUP BY activity
HAVING COUNT(*) != (SELECT COUNT(*) FROM Friends GROUP BY activity ORDER BY COUNT(*) ASC LIMIT 1)
AND COUNT(*) != (SELECT COUNT(*) FROM Friends GROUP BY activity ORDER BY COUNT(*) DESC LIMIT 1)

12. Number Of Trusted Contacts of a Customer 

SELECT invoice_id, customer_name, price, COUNT(contact_email) as contacts_cnt,
sum(IF(contact_email IN (SELECT DISTINCT email FROM Customers)1, 0)) as trusted_contacts_cnt
FROM Customers LEFT JOIN Contacts ON Customers.customer_id = Contacts.user_id
JOIN Invoices ON Customers.customer_id = Invoice.user_id
GROUP BY invoice_id
ORDER BY invoice_id

13. Active Users

SELECT id 
FROM Login a
JOIN Login b
ON a.id = b.id AND DATEDIFF(a.login_date, b.login_date) BETWEEN 1 AND 4
JOIN Accounts ON a.id = Accounts.id
GROUP BY a.id, a.login_date
HAVING COUNT(DISTINCT b.login_date) = 4

14. The Most Frequently Ordered Products for Each Customer

//고객별 젤 많이 구매한 상품(들)
SELECT customer_id, Products.product_id, Products.product_name 
FROM (
	SELECT customer_id, product_id, RANK() OVER (PARTITION BY customer_id ORDER BY order_count desc) r
	FROM(
		SELECT customer_id, product_id, COUNT(DISTINCT order_id) as order_count
		FROM Orders
		GROUP BY customer_id, product_id) order_counts) order_counts_ranked 
JOIN Product ON order_counts_ranked.product_id = Product_id
WHERE r = 1

15. Count Apples and Orange

SELECT SUM(Boxes.apple_count + COALESCE(Chest.apple_count, 0))as apple_count
, SUM(Boxes.orange_count + COALESCE(Chest.orange_count, 0))as orange_count
FROM Boxes
LEFT JOIN Chests 
ON Boxes.chest_id = Chests.chest_id

16. Grand Slam Titles (JOIN 조건 OR)

SELECT player_id, player_name, 
SUM(player_id = Wimbledon) + SUM(player_id = Au_open)
SUM(player_id = Fr_open) + SUM(player_id = US_open) as grand_slams_count
FROM Players
JOIN Chapionships
ON player_id = Wimbledon
OR player_id = Fr_open
OR player_id = US_open
OR player_id = Au_open
GROUP BY player_id

//잘못됨
SELECT player_id, player_name, COUNT(*) as grand_slams_count
FROM Players
LEFT JOIN Championships a
ON Players.player_id = a.Wimbledon
LEFT JOIN Championships b
ON Players.player_id = b.Fr_open
LEFT JOIN Championships c
ON Players.player_id = c.US_open
LEFT JOIN Championships d
ON Players.player_id = d.Au_open
GROUP BY player_id
-> 중복생김

17. Countries You Can Safely Invest In

//global 평균보다 높은 country
SELECT country.name as country
FROM Person 
JOIN Calls 
ON Person.id = Calls.caller_id OR Person.id = Calls.callee_id
JOIN Country
ON LEFT(Person.phone_number, 3) = Country.country_code
GROUP BY Country.name
HAVING AVG(duration) > (SELECT AVG(duration) **as wordwide_avg** FROM Calls)
//SUM(duration)/COUNT(*) = AVG(duration) 

18. Capital Gain/Loss

SELECT stock_name, 
SUM(IF(operation = 'Buy', price, 0))-SUM(IF(operation = 'Sell', price, 0))as capital_gain_loss
FROM Stocks
GROUP BY stock_name
ORDER BY capital_gain_loss

19. Team Scores in Football Tournament

SELECT team_id, team_name, 
SUM(IF(team_id = host_id AND host_goals > guest_goals, 3.0))
+SUM(IF(team_id = guest_id AND host_goals < guest_goals, 3.0))
+SUM(IF(team_id = host_id AND host_goals = guest_goals, 1.0))
+SUM(IF(team_id = host_id AND host_goals = guest_goals, 1.0))as num_points
FROM Teams
LEFT JOIN Matches //MATCH안한팀있을수도 있으니까
ON Teams.team_id = Matches.host_id 
OR Teams.team_id = Matches.guest_id 
GROUP BY team_id // 팀별 sum이니까 안적으면 전체 sum됨
ORDER BY num_points desc, team_id asc

20. Calculate Salaries

//연봉젤쎈사람이 1000$보다 낮으면 0
//1000 10000 사이면 24
//10000보다 크면 49
SELECT company_id, employee_id, employee_name, ROUND(salary - (salary*tax_rate/100)) as salary
FROM Salary s
LEFT JOIN (
	SELECT IF(MAX(salary)>10000, 49, IF(MAX(salary)>1000, 24, 0)) AS tax_rate
	FROM Salary
	GROUP BY company_id) m
ON s.company_id = m.company_id

21. Last Person to Fit in the Elevator 

SELECT person_name
FROM Queue a
JOIN Queue b
ON a.turn_id >= b.turn_id
GROUP BY a.turn_id
HAVING SUM(b.weight) <= 1000
ORDER BY turn_id desc
LIMIT 1

22. Number of Calls Between Two Person (LEAST, GREATEST)

SELECT LEAST(from_id, to_id) as person1
, GREATEST(from_id, to_id) as person 2
,COUNT(*) as call_duration,SUM(duration) as total_duration,
FROM Calls
GROUP BY from_id