❗ All queries are statements for PostgreSQL database.
👉 https://leetcode.com/problems/recyclable-and-low-fat-products/description/
SELECT product_id FROM products WHERE low_fats = 'Y' AND recyclable = 'Y'; 👉 https://leetcode.com/problems/find-customer-referee/description/
SELECT name FROM customer WHERE customer.referee_id <> 2; ❗ From PostgreSQL documentation:
<> is the standard SQL notation for “not equal”. != is an alias, which is converted to <> at a very early stage of parsing. Hence, it is not possible to implement != and <> operators that do different things.
👉 https://leetcode.com/problems/big-countries/description/
SELECT name, population, area FROM world WHERE area >= 3000000 OR population >= 25000000; 👉 https://leetcode.com/problems/article-views-i/description/
SELECT DISTINCT ON (author_id) author_id AS id FROM views WHERE author_id = viewer_id; 👉 https://leetcode.com/problems/invalid-tweets/description/
SELECT tweet_id FROM tweets WHERE length(content) > 15; 👉 https://leetcode.com/problems/replace-employee-id-with-the-unique-identifier/description
❗ A table order in the FROM clause is important.
- RIGHT JOIN
SELECT name, unique_id FROM employeeUNI RIGHT JOIN employees ON employeeUNI.id = employees.id; - LEFT JOIN
SELECT name, unique_id FROM employees LEFT JOIN employeeUNI ON employeeUNI.id = employees.id;
👉 https://leetcode.com/problems/product-sales-analysis-i/description
INNER JOIN
SELECT product_name, year, price FROM Sales JOIN product ON sales.product_id = product.product_id; 👉 https://leetcode.com/problems/customer-who-visited-but-did-not-make-any-transactions/
LEFT JOIN, GROUP BY, COUNT
SELECT v.customer_id, COUNT(*) AS count_no_trans FROM visits v LEFT JOIN transactions t ON v.visit_id = t.visit_id WHERE t.transaction_id IS NULL GROUP BY v.customer_id; 👉 https://leetcode.com/problems/rising-temperature/description/
INNER JOIN, INTERVAL
❗ Data/Time functions and operators in PostgreSQL documentation: https://www.postgresql.org/docs/current/functions-datetime.html
SELECT w1.id FROM weather w1 JOIN weather w2 ON w1.recordDate = w2.recordDate + INTERVAL '1 DAY' WHERE w1.temperature > w2.temperature; 👉 https://leetcode.com/problems/average-time-of-process-per-machine/description/
INNER JOIN, ROUND, AVG, CAST, GROUP BY
SELECT a.machine_id, ROUND(AVG(CAST(b.timestamp - a.timestamp AS DECIMAL)), 3) AS processing_time FROM activity a JOIN activity b ON a.machine_id = b.machine_id AND a.process_id = b.process_id AND a.activity_type = 'start' AND b.activity_type = 'end' GROUP BY a.machine_id;