SQL Algorithm
LeetCode
1757. Recyclable and Low Fat Products
| Column Name |
Type |
| product_id |
int |
| low_fats |
enum |
| recyclable |
enum |
- product_id is the primary key (column with unique values) for this table.
- low_fats is an ENUM (category) of type ('Y', 'N') where 'Y' means this product is low fat and 'N' means it is not.
- recyclable is an ENUM (category) of types ('Y', 'N') where 'Y' means this product is recyclable and 'N' means it is not.
- Write a solution to find the ids of products that are both low fat and recyclable.
- Return the result table in any order.
SELECT product_id
FROM Products
WHERE low_fats = 'Y' AND recyclable = 'Y';
511. Game Play Analysis I
| Column Name |
Type |
| player_id |
int |
| device_id |
int |
| event_date |
date |
| games_played |
int |
- (player_id, event_date) is the primary key (combination of columns with unique values) of this table.
- This table shows the activity of players of some games.
- Each row is a record of a player who logged in and played a number of games (possibly 0) before logging out on someday using some device.
- Write a solution to find the first login date for each player.
- Return the result table in any order.
SELECT player_id , MIN(event_date) AS first_login
FROM Activity
GROUP BY player_id;
1193. Monthly Transactions I
| Column Name |
Type |
| id |
int |
| country |
varchar |
| state |
enum |
| amount |
int |
| trans_date |
date |
- id is the primary key of this table.
- The table has information about incoming transactions.
- The state column is an enum of type ["approved", "declined"].
- Write an SQL query to find for each month and country, the number of transactions and their total amount, the number of approved transactions and their total amount.
- Return the result table in any order.
SELECT
DATE_FORMAT(trans_date, '%Y-%m') AS month, country,
COUNT(*) AS trans_count, SUM(IF(state = 'approved', 1, 0)) 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;
https://leetcode.com/problems/recyclable-and-low-fat-products/
https://leetcode.com/problems/game-play-analysis-i/
https://leetcode.com/problems/monthly-transactions-i/