5.6 - Leetcode SQL List

Problem	Difficulty	Key Ideas
Confirmation Rate	Medium	AVG() to get confirmation rate ROUND([value], 2) to round to 2dp COALESCE([value], 0) to provide a fallback value to 0 if value is null action = ‘confirmed’ will give true or false, which is 1 and 0 respectively in MySQL
Percentage of Users Attended a Contest	Easy	Subquery SELECT COUNT(user_id) FROM users to count to number of users
Queries Quality and Percentage	Easy	Use CASE WHEN rating < 3 THEN 1 ELSE 0 END and take the sum * 100 to find poor query percentage
Monthly Transaction I	Medium	LEFT(trans_date, 7) to get the first 7 characters from the left
Immediate Food Delivery II	Medium	Use a Common Table Expression (CTE) to store a temporary table result which can be used later on, with the syntax of: WITH temp_table AS ( SELECT query ) SELECT * FROM temp_table Use min(order_date) to the get the first order Can also use WHERE (customer_id, order_date) in (SELECT * FROM first_orders) to also match the fields in delivery to the first_order table instead of a join or CTE Bug faced: getting the customer preferred date in the CTE, but the SQL used does not get the correct preferred date when using group by
Game Play Analysis IV	Medium	Use a subquery to get all the first date of log in and left join the original table on the condition that the user has logged in a day after using DATEDIFF(enddate, startdate) = 1
User Activity for the Past 30 Days I	Easy	Use DATEDIFF('2019-07-27', activity_date) < 30 to ensure that the difference between the 2 dates is not more than 30 days ago Use COUNT(DISTINCT user_id) to get the distinct user_id
Product Sales Analysis III	Medium	Use subquery and combination to get only the product_id in the first year SELECT ... WHERE (product_id, year) IN (SELECT product_id, MIN(year) AS year FROM Sales GROUP BY product_id)
Classes With at Least 5 Students	Easy	Use GROUP BY class HAVING COUNT(student) > 5 to get all classes with at least 5 students
Biggest Single Number	Easy	Filter out num with count != 1 with IF(COUNT(num) = 1, num, null and desc order num and limit 1 Note: cannot use aggregate functions in where clause because it computes across multiple rows, while where clause filters individual rows
Triangle Judgement	Easy	Use formula that each side of the triangle must be less than the other 2 added up, ie x + y > z IF(condition, true, false) to change the condition into a variable
Consecutive Numbers	Medium	Inner join the Logs table 3 times based on similar numbers and if the numbers are consecutive
Product Price at a Given Date	Medium	Approach 1: Create 2 separate tables and union them: 1 - Get all product_ids that have minimum change_date after the required date and set the price to 10 2 - Get all the product_id and change_date where the date is before the date and get the max date Approach 2: Get all the latest prices before the date and use a select … where (product_id, change_date) in latest_prices. Then select all distinct product_id (Table a) and left join with this new table (Table b), and select a.product_id, COLESCE(b.new_price, 10)
Last Person to Fit in the Bus	Medium	Self join the table on the condition that the turn a is >= turn b, and sum b.weights grouped by a.turn. Order descending by turn, and limit 1 to get the last person and select the person_name
Count Salary Categories	Medium	Create 3 separate tables to count the number of accounts for each category and union them together
Exchange Seats	Medium	Use CASE WHEN … THEN … END to swap the id of adjacent rows, and also ensuring that id + 1 exists in the table
Movie Rating	Medium	EXTRACT(YEAR_MONTH FROM created_at) = 202002 to obtain only the year and month from the field
Fix Names in a Table	Easy	Use LEFT(name, 1) to get the first letter of the string, and use UPPER() to make it uppercase To make all the other characters lowercase, LOWER(RIGHT(name, LENGTH(name) - 1)) Use CONCAT() to join the results Other way: use SUBSTRING() function (1-indexed)
Delete Duplicate Emails	Easy	Delete syntax is selecting the entire table to delete based on a WHERE condition, DELETE (table) FROM (table) a, (table) b WHERE ...
Group Sold Products By The Date	Easy	Use GROUP_CONCAT to group data from multiple rows GROUP_CONCAT ( [DISTINCT] col_name1 [ORDER BY clause] [SEPARATOR str_val] )