Useful SQL Query

GROUP_CONCAT

# Leetcode 1484
select sell_date, count(*) as num_sold, 
group_concat(product order by product) as products
from

(select distinct sell_date, product
from Activities
) as new_table

group by sell_date;

If Statement

#leetcode 1407
select name, if (sum(distance) is null, 0, sum(distance)) as travelled_distance 
from Users
left join Rides on (Rides.user_id = Users.id)
group by Users.id
order by travelled_distance  desc, name asc;

Case Statement

# partial solution of Leetcode 1179. Sytax case when ... then ..
#when .. then ... else ... end
select id,
    sum(case when month="Jan" then revenue else null end) as Jan_Revenue
from Department
group by id;

count(distinct id)

select teacher_id, count(distinct subject_id) as cnt
from Teacher
group by teacher_id;