Find products that are both low fat and recyclable.

SELECT product_id
FROM Products
WHERE low_fats = 'Y'
  AND recyclable = 'Y';

Find names of customers not referred by customer with id = 2.

SELECT name
FROM Customer
WHERE referee_id != 2 
   OR referee_id IS NULL;

Find name, population, and area of big countries (area >= 3M or population >= 25M).

SELECT name, population, area
FROM World
WHERE area >= 3000000
   OR population >= 25000000;

Find authors who viewed their own articles.

SELECT DISTINCT author_id AS id
FROM Views
WHERE author_id = viewer_id
ORDER BY author_id;

Find tweet IDs with content longer than 15 characters.

SELECT tweet_id
FROM Tweets
WHERE LENGTH(content) > 15;

Show unique_id and name for all employees.

SELECT eu.unique_id, e.name
FROM Employees e
LEFT JOIN EmployeeUNI eu ON e.id = eu.id;

Report product_name, year, and price for each sale.

SELECT p.product_name, s.year, s.price
FROM Sales s
LEFT JOIN Product p ON s.product_id = p.product_id;

Find customer_id and count of visits without transactions.

SELECT customer_id, COUNT(*) AS count_no_trans
FROM Visits
WHERE visit_id NOT IN (
    SELECT DISTINCT visit_id FROM Transactions
)
GROUP BY customer_id;

Find IDs with higher temperature than previous day.

SELECT w1.id
FROM Weather w1
JOIN Weather w2 
  ON DATEDIFF(w1.recordDate, w2.recordDate) = 1
WHERE w1.temperature > w2.temperature;

Calculate average processing time per machine.

SELECT machine_id, 
       ROUND(AVG(end_time - start_time), 3) AS processing_time
FROM (
    SELECT machine_id, process_id,
           MAX(CASE WHEN activity_type = 'start' 
               THEN timestamp END) AS start_time,
           MAX(CASE WHEN activity_type = 'end' 
               THEN timestamp END) AS end_time
    FROM Activity
    GROUP BY machine_id, process_id
) AS subq
GROUP BY machine_id;

Report name and bonus of employees with bonus < 1000.

SELECT e.name, b.bonus
FROM Employee e
LEFT JOIN Bonus b ON e.empId = b.empId
WHERE b.bonus < 1000 OR b.bonus IS NULL;

Find number of exams each student attended for each subject.

SELECT s.student_id, s.student_name, 
       sub.subject_name, 
       COUNT(e.subject_name) AS attended_exams
FROM Students s
CROSS JOIN Subjects sub
LEFT JOIN Examinations e 
  ON s.student_id = e.student_id 
  AND sub.subject_name = e.subject_name
GROUP BY s.student_id, s.student_name, sub.subject_name
ORDER BY s.student_id, sub.subject_name;

Find managers with at least 5 direct reports.

SELECT name
FROM Employee
WHERE id IN (
    SELECT managerId
    FROM Employee
    GROUP BY managerId
    HAVING COUNT(*) >= 5
);

Calculate confirmation rate for each user.

SELECT s.user_id,
       ROUND(COALESCE(
           SUM(CASE WHEN c.action = 'confirmed' THEN 1 END) 
           / COUNT(c.action), 0), 2) AS confirmation_rate
FROM Signups s
LEFT JOIN Confirmations c ON s.user_id = c.user_id
GROUP BY s.user_id;

Report movies with odd ID, not boring description, ordered by rating.

SELECT *
FROM Cinema
WHERE id % 2 = 1 
  AND description != 'boring'
ORDER BY rating DESC;

Find average selling price for each product.

SELECT p.product_id,
       ROUND(SUM(p.price * u.units) / SUM(u.units), 2) 
       AS average_price
FROM Prices p
LEFT JOIN UnitsSold 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;

Report average experience years for each project.

SELECT p.project_id, 
       ROUND(AVG(e.experience_years), 2) AS average_years
FROM Project p
LEFT JOIN Employee e ON p.employee_id = e.employee_id
GROUP BY p.project_id;

Find percentage of users registered for each contest.

SELECT contest_id,
       ROUND(COUNT(DISTINCT user_id) * 100.0 
           / (SELECT COUNT(*) FROM Users), 2) AS percentage
FROM Register
GROUP BY contest_id
ORDER BY percentage DESC, contest_id ASC;

Calculate quality and poor_query_percentage for each query.

SELECT query_name,
       ROUND(AVG(rating / position), 2) AS quality,
       ROUND(SUM(CASE WHEN rating < 3 THEN 1 ELSE 0 END) 
           * 100.0 / COUNT(*), 2) AS poor_query_percentage
FROM Queries
WHERE query_name IS NOT NULL
GROUP BY query_name;

Find number of transactions and amounts per month per country.

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

Find percentage of immediate orders among first orders.

SELECT ROUND(
    AVG(CASE WHEN order_date = customer_pref_delivery_date 
        THEN 1 ELSE 0 END) * 100, 2
) AS immediate_percentage
FROM Delivery
WHERE (customer_id, order_date) IN (
    SELECT customer_id, MIN(order_date)
    FROM Delivery
    GROUP BY customer_id
);

Find fraction of players who logged in the day after first login.

SELECT ROUND(
    COUNT(DISTINCT a2.player_id) 
    / COUNT(DISTINCT a1.player_id), 2
) AS fraction
FROM (
    SELECT player_id, MIN(event_date) AS first_login
    FROM Activity
    GROUP BY player_id
) a1
LEFT JOIN Activity a2 
  ON a1.player_id = a2.player_id
  AND DATEDIFF(a2.event_date, a1.first_login) = 1;

Count unique subjects per teacher.

SELECT teacher_id, 
       COUNT(DISTINCT subject_id) AS cnt
FROM Teacher
GROUP BY teacher_id;

Find daily active users in the past 30 days.

SELECT activity_date AS day, 
       COUNT(DISTINCT user_id) AS active_users
FROM Activity
WHERE activity_date BETWEEN 
    DATE_SUB('2019-07-27', INTERVAL 29 DAY) 
    AND '2019-07-27'
GROUP BY activity_date;

Select product_id, first year sold, quantity, and price.

SELECT product_id, year AS first_year, quantity, price
FROM Sales
WHERE (product_id, year) IN (
    SELECT product_id, MIN(year)
    FROM Sales
    GROUP BY product_id
);

Find classes with at least 5 students.

SELECT class
FROM Courses
GROUP BY class
HAVING COUNT(student) >= 5;

Count followers for each user.

SELECT user_id, 
       COUNT(DISTINCT follower_id) AS followers_count
FROM Followers
GROUP BY user_id
ORDER BY user_id;

Find largest number that appears only once.

SELECT MAX(num) AS num
FROM (
    SELECT num
    FROM MyNumbers
    GROUP BY num
    HAVING COUNT(num) = 1
) AS single_numbers;

Find customers who bought all products.

SELECT customer_id
FROM Customer
GROUP BY customer_id
HAVING COUNT(DISTINCT product_key) = (
    SELECT COUNT(*) FROM Product
);

Report employee info for those who have reports.

SELECT e1.employee_id, e1.name, 
       COUNT(e2.employee_id) AS reports_count,
       ROUND(AVG(e2.age)) AS average_age
FROM Employees e1
JOIN Employees e2 ON e1.employee_id = e2.reports_to
GROUP BY e1.employee_id, e1.name
ORDER BY e1.employee_id;

Report primary department for each employee.

SELECT employee_id, department_id
FROM Employee
WHERE primary_flag = 'Y'
   OR employee_id IN (
       SELECT employee_id
       FROM Employee
       GROUP BY employee_id
       HAVING COUNT(*) = 1
   );

Check if three lengths can form a triangle.

SELECT x, y, z,
       CASE 
           WHEN x + y > z AND x + z > y AND y + z > x 
           THEN 'Yes'
           ELSE 'No'
       END AS triangle
FROM Triangle;

Find numbers that appear at least three times consecutively.

SELECT DISTINCT l1.num AS ConsecutiveNums
FROM Logs l1
JOIN Logs l2 ON l1.id = l2.id - 1 AND l1.num = l2.num
JOIN Logs l3 ON l1.id = l3.id - 2 AND l1.num = l3.num;

Find prices of all products on 2019-08-16.

SELECT product_id, new_price AS price
FROM Products
WHERE (product_id, change_date) IN (
    SELECT product_id, MAX(change_date)
    FROM Products
    WHERE change_date <= '2019-08-16'
    GROUP BY product_id
)
UNION
SELECT product_id, 10 AS price
FROM Products
WHERE product_id NOT IN (
    SELECT product_id
    FROM Products
    WHERE change_date <= '2019-08-16'
);

Find last person to fit without exceeding 1000 kg.

SELECT person_name
FROM (
    SELECT person_name, 
           SUM(weight) OVER (ORDER BY turn) AS cumulative_weight
    FROM Queue
) AS q
WHERE cumulative_weight <= 1000
ORDER BY cumulative_weight DESC
LIMIT 1;

Count accounts in each salary category.

SELECT 'Low Salary' AS category, 
       SUM(CASE WHEN income < 20000 THEN 1 ELSE 0 END) 
       AS accounts_count
FROM Accounts
UNION
SELECT 'Average Salary' AS category,
       SUM(CASE WHEN income BETWEEN 20000 AND 50000 
           THEN 1 ELSE 0 END) AS accounts_count
FROM Accounts
UNION
SELECT 'High Salary' AS category,
       SUM(CASE WHEN income > 50000 THEN 1 ELSE 0 END) 
       AS accounts_count
FROM Accounts;

Find employees with salary < 30000 whose manager left.

SELECT employee_id
FROM Employees
WHERE salary < 30000
  AND manager_id NOT IN (
      SELECT employee_id FROM Employees
  )
ORDER BY employee_id;

Swap seat IDs of consecutive students.

SELECT id,
       CASE 
           WHEN id % 2 = 1 AND id = (SELECT MAX(id) FROM Seat) 
               THEN student
           WHEN id % 2 = 1 
               THEN LEAD(student) OVER (ORDER BY id)
           ELSE LAG(student) OVER (ORDER BY id)
       END AS student
FROM Seat;

Find user with most ratings and highest-rated movie in Feb 2020.

(SELECT u.name AS results
 FROM MovieRating mr
 JOIN Users u ON mr.user_id = u.user_id
 GROUP BY u.name
 ORDER BY COUNT(*) DESC, u.name
 LIMIT 1)
UNION ALL
(SELECT m.title AS results
 FROM MovieRating mr
 JOIN Movies m ON mr.movie_id = m.movie_id
 WHERE DATE_FORMAT(mr.created_at, '%Y-%m') = '2020-02'
 GROUP BY m.title
 ORDER BY AVG(mr.rating) DESC, m.title
 LIMIT 1);

Calculate 7-day moving average of customer amounts.

SELECT visited_on,
       amount,
       ROUND(amount / 7, 2) AS average_amount
FROM (
    SELECT DISTINCT visited_on,
           SUM(amount) OVER (
               ORDER BY visited_on 
               RANGE BETWEEN INTERVAL 6 DAY PRECEDING 
               AND CURRENT ROW
           ) AS amount,
           MIN(visited_on) OVER () AS first_date
    FROM Customer
) t
WHERE visited_on >= DATE_ADD(first_date, INTERVAL 6 DAY);

Find person with most friends.

SELECT id, COUNT(*) AS num
FROM (
    SELECT requester_id AS id FROM RequestAccepted
    UNION ALL
    SELECT accepter_id AS id FROM RequestAccepted
) AS friends
GROUP BY id
ORDER BY num DESC
LIMIT 1;

Find sum of investments for specific conditions.

SELECT ROUND(SUM(tiv_2016), 2) AS tiv_2016
FROM Insurance
WHERE tiv_2015 IN (
    SELECT tiv_2015
    FROM Insurance
    GROUP BY tiv_2015
    HAVING COUNT(*) > 1
)
AND (lat, lon) IN (
    SELECT lat, lon
    FROM Insurance
    GROUP BY lat, lon
    HAVING COUNT(*) = 1
);

Find top 3 salaries in each department.

SELECT d.name AS Department,
       e.name AS Employee,
       e.salary AS Salary
FROM Employee e
JOIN Department d ON e.departmentId = d.id
WHERE (
    SELECT COUNT(DISTINCT e2.salary)
    FROM Employee e2
    WHERE e2.departmentId = e.departmentId
      AND e2.salary >= e.salary
) <= 3;

Fix names to have first letter capitalized.

SELECT user_id,
       CONCAT(UPPER(SUBSTRING(name, 1, 1)), 
              LOWER(SUBSTRING(name, 2))) AS name
FROM Users
ORDER BY user_id;

Find patients with Type I Diabetes (DIAB1).

SELECT patient_id, patient_name, conditions
FROM Patients
WHERE conditions LIKE 'DIAB1%' 
   OR conditions LIKE '% DIAB1%';

Delete duplicate emails keeping lowest id.

DELETE p1
FROM Person p1
JOIN Person p2 
  ON p1.email = p2.email 
  AND p1.id > p2.id;

Find second highest salary.

SELECT (
    SELECT DISTINCT salary
    FROM Employee
    ORDER BY salary DESC
    LIMIT 1 OFFSET 1
) AS SecondHighestSalary;

Group products sold per day.

SELECT sell_date,
       COUNT(DISTINCT product) AS num_sold,
       GROUP_CONCAT(DISTINCT product ORDER BY product) 
           AS products
FROM Activities
GROUP BY sell_date
ORDER BY sell_date;

Find products with >= 100 units ordered in Feb 2020.

SELECT p.product_name, 
       SUM(o.unit) AS unit
FROM Products p
JOIN Orders o ON p.product_id = o.product_id
WHERE DATE_FORMAT(o.order_date, '%Y-%m') = '2020-02'
GROUP BY p.product_name
HAVING SUM(o.unit) >= 100;

Find users with valid LeetCode emails.

SELECT *
FROM Users
WHERE mail REGEXP '^[A-Za-z][A-Za-z0-9_.-]*@leetcode\\.com;