| Reference |
Leetcode SQL problems |
| Use having with group-by |
group by class having count(distinct student)>4 Leecode: Classes More Than 5 Students |
| Update records with case clause |
set sex = case when sex = 'm' then 'f' else 'm' end LeetCode: Swap Salary |
| Sum with case |
sum(case when state = 'approved' then 1 else 0 end) LeetCode: Monthly Transactions I |
| Subquery |
where (customer_id, order_date) in (select ... LeetCode: Immediate Food Delivery II |
| Sql isnull and if |
if(s1.id%2=0, s1.student, if(isnull(s2.student), s1.student, s2.student)) |
| round float |
(round(accepts/requests, 2) Leecode: Friend Requests I: Overall Acceptance Rate |
| Rank score |
(select @rank := 0, @prev := -1) as rows LeetCode: Rank Scores |
| Select clause add an incremental id |
select (@cnt1 := @cnt1 + 1) as id LeetCode: Students Report By Geography |
| MySQL DATE_SUB |
DATE_SUB(record_date, INTERVAL 6 DAY) |
| Get accumulated sum for each group |
LeetCode: Last Person to Fit in the Elevator |
| nth element in each group |
LeetCode: Nth Highest Salary, LeetCode: Get the Second Most Recent Activity |
| topn entris for each group |
LeetCode: Department Top Three Salaries |
union vs union all |
LeetCode: Friend Requests II: Who Has the Most Friends |
| Convert float to int |
cast(amount as SIGNED) |
| If no match show null |
LeetCode: Second Highest Salary |
| Get maximum of multiple columns |
LeetCode: Triangle Judgement |
| Tree hierachy in SQL |
LeetCode: All People Report to the Given Manager |
| Mysql distinct in having clause |
LeetCode: Active Users |
| mysql doesn’t support full outer join |
LeetCode: Monthly Transactions II |
| When left join+groupby, group by which tables’ field? |
LeetCode: Team Scores in Football Tournament |
sum(if(isnull( vs ifnull(sum( |
LeetCode: Team Scores in Football Tournament |
| Three tables’ inner join |
LeetCode: Students and Examinations |
| Find Continuous Ranges |
Find the Start and End Number of Continuous Ranges |
| Impressive sql problems |
Leecode: Find Median Given Frequency of Numbers, LeetCode: Consecutive Available Seats |
| Impressive sql problems |
LeetCode: Rank Scores, LeetCode: Exchange Seats, LeetCode: Students Report By Geography |
