Daily LeetCode #7

Some notes on problems from SQL 50.

https://leetcode.com/problems/average-selling-price/submissions/1424623318/?envType=study-plan-v2&envId=top-sql-50

SELECT P.product_id, 
       IFNULL(round(sum(P.price * U.units) / sum(U.units), 2), 0) AS average_price
FROM Prices AS P LEFT OUTER JOIN UnitsSold AS U ON P.product_id = U.product_id AND U.purchase_date BETWEEN P.start_date AND P.end_date
GROUP BY P.product_id

Notes:

• the result should include all the product_id, so we need to use LEFT OUTER JOIN, otherwise we will lose those ids with NULL in average_price
• combine with IFNULL, NULL can be converted into 0
• function a BETWEEN b AND c: check if date variable a in between date c and date d

https://leetcode.com/problems/monthly-transactions-i/description/?envType=study-plan-v2&envId=top-sql-50

SELECT SUBSTR(T.trans_date, 1, 7) AS month, 
       T.country, count(*) AS trans_count, 
       sum(IF(T.state = "approved", 1, 0)) AS approved_count, 
       sum(T.amount) AS trans_total_amount, 
       sum(IF(T.state = "approved", T.amount, 0)) AS approved_total_amount
FROM Transactions AS T
GROUP BY month, country

• use SUBSTR(a, positive: start from beginning; negative: start from the end, length) to extract year-month from date attribute
• can use EXTRACT(MONTH FROM date) to extract only month
• can use EXTRACT(YEAR FROM date) to extract only year