Luyện SQL với leetcode

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

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

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

UPDATE Salary
SET sex = IF(sex = 'm', 'f', 'm');

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

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

SELECT DATE_FORMAT(day, '%W, %M %e, %Y') AS day
FROM Days;

SELECT customer_id
FROM Customers
WHERE year = 2021 AND revenue > 0;

SELECT problem_id
FROM Problems
WHERE likes / (likes + dislikes) < 0.6
ORDER BY problem_id;

SELECT COUNT(DISTINCT customer_id) AS rich_count
FROM Store 
WHERE amount > 500;

SELECT * FROM Olympic
ORDER BY gold_medals, silver_medals, bronze_medals, country;

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

SELECT uni.unique_id AS unique_id, es.name AS name
FROM Employees es
left JOIN Employeeuni uni ON es.id = uni.id;

SELECT product_id, 'store1' AS store, store1 AS price
FROM Products WHERE store1 IS NOT NULL
UNION
SELECT product_id, 'store2' AS store, store2 AS price
FROM Products WHERE store2 IS NOT NULL
UNION
SELECT product_id, 'store3' AS store, store3 AS price
FROM Products WHERE store3 IS NOT NULL;

SELECT u.name, SUM(t.amount) AS balance
FROM Users u
JOIN Transactions t ON u.account = t.account
GROUP BY t.account
HAVING balance > 10000;

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

SELECT id, 
SUM(IF (month = 'Jan', revenue, null)) AS Jan_Revenue,
SUM(IF (month = 'Feb', revenue, null)) AS Feb_Revenue,
SUM(IF (month = 'Mar', revenue, null)) AS Mar_Revenue,
SUM(IF (month = 'Apr', revenue, null)) AS Apr_Revenue,
SUM(IF (month = 'May', revenue, null)) AS May_Revenue,
SUM(IF (month = 'Jun', revenue, null)) AS Jun_Revenue,
SUM(IF (month = 'Jul', revenue, null)) AS Jul_Revenue,
SUM(IF (month = 'Aug', revenue, null)) AS Aug_Revenue,
SUM(IF (month = 'Sep', revenue, null)) AS Sep_Revenue,
SUM(IF (month = 'Oct', revenue, null)) AS Oct_Revenue,
SUM(IF (month = 'Nov', revenue, null)) AS Nov_Revenue,
SUM(IF (month = 'Dec', revenue, null)) AS Dec_Revenue
FROM Department
GROUP BY id;

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

SELECT user_id, MAX(time_stamp) AS last_stamp
FROM Logins
WHERE YEAR(time_stamp) = 2020
GROUP BY user_id;

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

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

SELECT employee_id, COUNT(employee_id) OVER (PARTITION BY team_id) AS team_size
FROM Employee;

SELECT w.name AS warehouse_name, SUM(w.units * p.width * p.length * p.height) AS volumn
FROM Warehouse w JOIN Products p ON w.product_id = p.product_id
GROUP BY w.name;

SELECT t1.team_name AS home_team, t2.team_name AS away_team
FROM Teams t1
CROSS JOIN Teams t2
WHERE t1.team_name != t2.team_name;

SELECT a.student_name AS member_A, b.student_name AS member_B, c.student_name AS member_C
FROM SchoolA a, SchoolB b, SchoolC c
WHERE a.student_name != b.student_name 
  AND a.student_name != c.student_name
  AND b.student_name != c.student_name
  AND a.student_id != b.student_id
  AND a.student_id != c.student_id
  AND b.student_id != c.student_id;

SELECT q.id, q.year, IFNULL(npv, 0) AS npv
FROM Queries q 
LEFT JOIN NPV n ON q.id = n.id AND q.year = n.year;

SELECT product_id, SUM(quantity) AS total_quantity
FROM Sales;

SELECT event_day AS day, emp_id, SUM(out_time - in_time) AS total_time
FROM Employees
GROUP BY day, emp_id;

SELECT date_id, make_name, COUNT(DISTINCT lead_id) AS unique_leads, 
COUNT(DISTINCT partner_id) AS unique_partners
FROM DailySales
GROUP BY date_id, make_name;

SELECT id, name
FROM Students
WHERE department_id NOT IN (SELECT id FROM Departments);

SELECT product_id, 
SUM(IF (store = 'store1', price, null)) AS store1,
SUM(IF (store = 'store2', price, null)) AS store2,
SUM(IF (store = 'store3', price, null)) AS store3
FROM Products
GROUP BY product_id;

SELECT DATE_FORMAT(order_date, '%Y-%m') AS month, COUNT(DISTINCT order_id) AS order_count, COUNT(DISTINCT customer_id) AS customer_count
FROM Orders
GROUP BY month;

SELECT ROUND(100 * AVG(IF (order_date = customer_pref_delivery_date)), 2) AS immediate_percentage
FROM Delivery;

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

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

SELECT *, IF(x + y > z AND y + z > x AND z + x > y, 'Yes', 'No') AS triangle
FROM Triangle;

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

SELECT actor_id, director_id
FROM ActorDirector
GROUP BY actor_id, director_id
HAVING COUNT(DISTINCT timestamp) >= 3;

SELECT player_id, MIN(event_date) AS first_login
FROM Activity
GROUP BY player_id;

SELECT p.firstName, p.lastName, a.city, a.state
FROM Person p
LEFT JOIN Address a ON p.personId = a.personId;

SELECT seller_id
FROM 
(
   SELECT seller_id, RANK() OVER (PARTITION BY seller_id ORDER BY SUM(price) DESC) AS rank
   FROM Sales
   GROUP BY seller_id;
) x WHERE rank = 1;

WITH Student AS (
   SELECT COUNT(*) AS NY FROM NewYork WHERE score >= 90
   UNION
   SELECT COUNT(*) AS CA FROM California WHERE score >= 90
)
SELECT CASE 
   WHEN NY > CA THEN 'New York University'
   WHEN CA > NY THEN 'California University'
   ELSE 'No Winner'
END AS winner
FROM Student;