LC 1173 & LC 1193 & LC 1211 & LC 1633

SQL-Basic
SQL
Publish Date:   2022-03-22

The following basic-level SQL problems are about ‘Function’. Summarizing values by using SUM, AVG, with IF or conditions.

LeetCode 1173

Immediate Food Delivery I (Easy) [link]

SELECT ROUND(SUM(order_date = customer_pref_delivery_date) / COUNT(*) * 100,2) AS immediate_percentage
FROM Delivery;

LeetCode 1193

Monthly Transactions I (Medium) [link]

Note that SUM(state = 'approved') and COUNT(IF(state = 'approved', 1, NULL)) are equivalent.

SELECT DATE_FORMAT(trans_date, '%Y-%m') AS month, 
    country, 
    COUNT(*) AS trans_count, 
    # SUM(state = 'approved') AS approved_count,
    COUNT(IF(state = 'approved', 1, NULL)) AS approved_count,
    SUM(amount) AS trans_total_amount,
    SUM(IF(state = 'approved', amount, 0)) AS approved_total_amount
FROM Transactions
GROUP BY month, country;

LeetCode 1211

Queries Quality and Percentage (Easy) [link]

SELECT query_name, ROUND(AVG(rating / position),2) AS quality, ROUND(SUM(rating < 3) / COUNT(*) * 100,2) AS poor_query_percentage
FROM Queries
GROUP BY query_name;

LeetCode 1633

Percentage of Users Attended a Contest (Easy) [link]

SELECT contest_id, ROUND(COUNT(user_id) / (SELECT COUNT(user_id) FROM Users) * 100, 2) AS percentage
FROM Register
GROUP BY contest_id
ORDER BY percentage DESC, contest_id;
SQL-Basic
  TOC