1907. Categorize Salary Counts

Table: Accounts

+-------------+------+
| Column      | Type |
+-------------+------+
| account_id  | int  |
| income      | int  |
+-------------+------+
account_id is the primary key. Each row has monthly income of a bank account.

Query the number of accounts in each salary category:

• Low Salary: income < 20000
• Average Salary: 20000 ≤ income ≤ 50000
• High Salary: income > 50000

Result must include all three categories (use 0 if no accounts). Order is arbitrary.

Example Input/Output:

Input: Accounts table
+------------+--------+
| account_id | income |
+------------+--------+
| 3          | 108939 |
| 2          | 12747  |
| 8          | 87709  |
| 6          | 91796  |
+------------+--------+
Output:
+----------------+----------------+ 
| category       | accounts_count |
+----------------+----------------+ 
| Low Salary     | 1              |
| Average Salary | 0              |
| High Salary    | 3              |
+----------------+----------------+ 

SQL Solution (Alternative Approach):

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

1978. Employees with Departed Managers

Table: Employees

+-------------+----------+
| Column      | Type     |
+-------------+----------+
| employee_id | int      |
| name        | varchar  |
| manager_id  | int      |
| salary      | int      |
+-------------+----------+
employee_id is the primary key. manager_id may be NULL (no manager).

Find employees with salary < 30000 and whose manager is no longer in the company (manager_id not present in Employees). Order by employee_id.

Example Input/Output:

Input: Employees table
+-------------+-----------+------------+--------+
| employee_id | name      | manager_id | salary |
+-------------+-----------+------------+--------+
| 3           | Mila      | 9          | 60301  |
| 12          | Antonella | null       | 31000  |
| 13          | Emery     | null       | 67084  |
| 1           | Kalel     | 11         | 21241  |
| 9           | Mikaela   | null       | 50937  |
| 11          | Joziah    | 6          | 28485  |
+-------------+-----------+------------+--------+
Output:
+-------------+
| employee_id |
+-------------+
| 11          |
+-------------+

SQL Solution (Using LEFT JOIN):

SELECT e.employee_id
FROM Employees e
LEFT JOIN Employees m ON e.manager_id = m.employee_id
WHERE e.salary < 30000 
  AND m.employee_id IS NULL  -- Manager not in table
  AND e.manager_id IS NOT NULL  -- Exclude employees with no manager
ORDER BY e.employee_id;

626. Swap Seats

Table: Seat

+-------------+---------+
| Column      | Type    |
+-------------+---------+
| id          | int     |
| student     | varchar |
+-------------+---------+
id is the primary key (consecutive integers).

Swap seats for every two consecutive students. If the number of students is odd, the last student remains in place. Return results ordered by id.

Example Input/Output:

Input: Seat table
+----+---------+
| id | student |
+----+---------+
| 1  | Abbot   |
| 2  | Doris   |
| 3  | Emerson |
| 4  | Green   |
| 5  | Jeames  |
+----+---------+
Output:
+----+---------+
| id | student |
+----+---------+
| 1  | Doris   |
| 2  | Abbot   |
| 3  | Green   |
| 4  | Emerson |
| 5  | Jeames  |
+----+---------+

SQL Solution (Using Window Function):

SELECT 
    CASE 
        WHEN id % 2 = 1 THEN id + 1
        ELSE id - 1
    END AS id, 
    student
FROM Seat
ORDER BY id;

-- Handle odd total count (last row remains)
-- Adjustment for odd count (if needed):
SELECT 
    CASE 
        WHEN id % 2 = 1 AND id < (SELECT MAX(id) FROM Seat) THEN id + 1
        WHEN id % 2 = 0 THEN id - 1
        ELSE id
    END AS id, 
    student
FROM Seat
ORDER BY id;

1341. Movie Rating

Tables: Movies, Users, MovieRating

Movies:
+-----------+---------+
| movie_id  | title   |
+-----------+---------+
(movie_id is primary key)

Users:
+-----------+---------+
| user_id   | name    |
+-----------+---------+
(user_id is primary key)

MovieRating:
+-----------+---------+--------+------------+
| movie_id  | user_id | rating | created_at |
+-----------+---------+--------+------------+
(movie_id, user_id) is primary key

Tasks:

1. Find the user with the most movie reviews. If tie, return lexicographically smallest name.
2. Find the movie with the highest average rating in February 2020. If tie, return lexicographically smallest title.

Example Input/Output:

Input (simplified):
MovieRating entries for Feb 2020:
+-----------+---------+--------+------------+
| movie_id  | user_id | rating | created_at |
+-----------+---------+--------+------------+
| 1         | 2       | 4      | 2020-02-11 |
| 1         | 3       | 2      | 2020-02-12 |
| 2         | 1       | 5      | 2020-02-17 |
| 2         | 2       | 2      | 2020-02-01 |
| 3         | 1       | 3      | 2020-02-22 |
| 3         | 2       | 4      | 2020-02-25 |
+-----------+---------+--------+------------+
Output:
+--------------+
| results      |
+--------------+
| Daniel       |  -- Most reviews (3) and lex smallest
| Frozen 2     |  -- Highest average (3.5) and lex smallest
+--------------+

SQL Solution (CTE Approach):

-- Task 1: User with most reviews
WITH UserReviews AS (
    SELECT u.name, COUNT(*) AS review_count
    FROM MovieRating mr
    JOIN Users u ON mr.user_id = u.user_id
    GROUP BY u.user_id, u.name
)
SELECT name AS results
FROM UserReviews
ORDER BY review_count DESC, name
LIMIT 1

UNION ALL

-- Task 2: Movie with highest Feb 2020 rating
WITH FebRatings AS (
    SELECT m.title, AVG(mr.rating) AS avg_rating
    FROM MovieRating mr
    JOIN Movies m ON mr.movie_id = m.movie_id
    WHERE mr.created_at BETWEEN '2020-02-01' AND '2020-02-29'
    GROUP BY m.movie_id, m.title
)
SELECT title AS results
FROM FebRatings
ORDER BY avg_rating DESC, title
LIMIT 1;

1321. Restaurant Revenue Growth

Table: Customer

+-------------+--------------+--------------+-------------+
| customer_id | name         | visited_on   | amount      |
+-------------+--------------+--------------+-------------+
(customer_id, visited_on) is primary key.

Calculate 7-day rolling average revenue (current date + 6 previous days). Only include dates ≥ (min date + 6). average_amount has 2 decimal places. Order by visited_on.

Example Input/Output:

Input (simplified):
Visits from 2019-01-01 to 2019-01-10.
Output (partial):
+--------------+--------------+----------------+ 
| visited_on   | amount       | average_amount |
+--------------+--------------+----------------+ 
| 2019-01-07   | 860          | 122.86         |
| 2019-01-08   | 840          | 120.00         |
+--------------+--------------+----------------+ 

SQL Solution (Window Function):

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