Table: Customer

• The id column is the primary key.
• Each row represents a customer, their name, and the id of the customer who referred them. If a customer wasn’t referred by anyone, the referee_id is NULL.

Problem:

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

The result can be returned in any order.

Example:

Input: Customer table:

Output:

In SQL, NULL is not equal to any value, including 2. Therefore, the condition referee_id != 2 will exclude records where referee_id is NULL, because comparisons with NULL always result in an unknown NULL value, which is neither TRUE nor FALSE. It is crucial to consider the handling of NULL values when working with databases.

Table: Views

• There is no primary key in this table, and the table may contain duplicate rows.
• Each row indicates that a viewer viewed an article, where the article is written by an author, on a specific date.
• If author_id and viewer_id are the same, it indicates that the author viewed their own article.

Problem:

Write a query to find the IDs of authors who have viewed at least one of their own articles.

Return the result table sorted by author_id in ascending order.

Example:

Input:

Views table:

Output:

In SQL, the DISTINCT keyword is used to remove duplicate records from the result set, ensuring that only unique values are returned. It is commonly used in scenarios such as aggregating distinct values and ensuring uniqueness in join queries.

# Aggregating unique values
SELECT DISTINCT product_id
FROM Order_Items;

# Ensuring unique results in join queries
SELECT DISTINCT c.customer_id, c.name
FROM Customers c
JOIN Orders o ON c.customer_id = o.customer_id;

Tables:

Employees

• id is the primary key for this table.
• Each row represents an employee in the company, containing their id and name.

EmployeeUNI

• (id, unique_id) is the primary key, meaning the combination of id and unique_id is unique across the table.
• Each row represents an employee’s id and their corresponding unique_id in the company.

Task:

Write a SQL query that returns the unique_id of each employee from the Employees table. If an employee does not have a corresponding unique_id, return NULL for that employee.

Query Requirements:

1. If an employee has a unique_id, show it.
2. If an employee does not have a unique_id, return NULL.
3. The result should display the unique_id and the name of each employee.

The results can be returned in any order.

Example:

Input:

Employees table:

EmployeeUNI table:

Output:

Explanation:

• Alice and Bob do not have a unique_id, so NULL is shown for both.
• Meir has a unique_id of 2.
• Winston has a unique_id of 3.
• Jonathan has a unique_id of 1.

Table: Signups

• user_id is a unique identifier for each user.
• Each row represents the signup time for the user identified by user_id.

Table: Confirmations

• The primary key is a combination of user_id and time_stamp.
• user_id is a foreign key referencing the Signups table.
• action is an ENUM with two possible values: 'confirmed' or 'timeout'.
• Each row represents a confirmation request made by the user with user_id, indicating whether the confirmation message was confirmed ('confirmed') or timed out ('timeout').

Problem Description

The confirmation rate of a user is calculated as the ratio of 'confirmed' actions to the total number of confirmation requests. If a user has not made any confirmation requests, the confirmation rate is 0. The rate should be rounded to two decimal places.

Goal

Write a SQL query to find the confirmation rate for each user.

Return the result in any order.

Input

Signups table:

Confirmations table:

Output

Explanation:

• User 6 did not request any confirmation messages, so their confirmation rate is 0.
• User 3 made two confirmation requests, but both timed out, so their confirmation rate is 0.
• User 7 made three requests, and all were confirmed, so their confirmation rate is 1.00.
• User 2 made two requests: one confirmed and one timed out, so their confirmation rate is 1/2 = 0.50.

• SUM(action = 'confirmed'): action = 'confirmed' returns 1 for confirmed actions and 0 for 'timeout' action.
• COUNT(1): Counts the total number of rows. (Use COUNT(*) for counting rows is more clear and idiomatic).
• LEFT JOIN Confirmations USING (user_id): Joins the Signups table with the Confirmations table on the user_id column.
• GROUP BY 1: A shorthand for GROUP BY user_id. It groups the results by the first column in the SELECT list, which is user_id in this case.

Tables:

Sales

• (sale_id, year) is the primary key, meaning this combination of columns is unique.
• product_id is a foreign key referencing the Product table.
• Each row represents a sale of a product identified by product_id in a particular year.
• The price is per unit of the product.

Product

• product_id is the primary key for this table.
• Each row represents a product with its product_name.

Task:

Write a SQL query to report the product_name, year, and price for each sale in the Sales table.

Query Requirements:

1. Return the product_name from the Product table, corresponding to the product_id in the Sales table.
2. Include the year and price from the Sales table for each sale.
3. The result can be returned in any order.

Example:

Input:

Sales table:

Product table:

Output:

Explanation:

• From sale_id = 1, we can conclude that Nokia was sold for 5000 in 2008.
• From sale_id = 2, we can conclude that Nokia was sold for 5000 in 2009.
• From sale_id = 7, we can conclude that Apple was sold for 9000 in 2011.

Table: Visits

• visit_id is the unique identifier for each visit.
• This table contains information about the customers who visited the mall.

Table: Transactions

• transaction_id is the unique identifier for each transaction.
• This table contains information about the transactions made during each visit.

Task:

Write a SQL query to find the IDs of customers who visited the mall without making any transactions, and count how many times they made these visits.

The result should include:

• customer_id — the ID of the customer.
• count_no_trans — the number of visits where no transaction was made.

Query Requirements:

1. Return the result sorted in any order.

Input:

Visits table:

Transactions table:

Output:

Explanation:

• Customer with id = 23 visited once and made a transaction during the visit with id = 12.
• Customer with id = 9 visited once and made a transaction during the visit with id = 13.
• Customer with id = 30 visited once and did not make any transactions.
• Customer with id = 54 visited three times. During two visits, they did not make any transactions, and during one visit, they made three transactions.
• Customer with id = 96 visited once and did not make any transactions.

In the output, customers with id = 30, id = 96, and id = 54 (for two of their visits) made visits without transactions.

In the above SQL query, MySQL first identifies the Visits table and performs a LEFT JOIN with the Transactions table based on the condition v.visit_id = t.visit_id. The LEFT JOIN operation combines the two tables and fills NULL in rows where there is no match in the Transactions table. The result looks like this:

SELECT *
FROM Visits v
LEFT JOIN Transactions t
ON v.visit_id = t.visit_id;

After joining the tables, MySQL applies the WHERE clause to filter rows where t.transaction_id IS NULL, which means visits without any transactions.

SELECT *
FROM Visits v
LEFT JOIN Transactions t
ON v.visit_id = t.visit_id
WHERE t.transaction_id IS NULL;

Next, MySQL groups the filtered rows by customer_id and calculates the number of visits per customer using the COUNT(v.visit_id) function.

SELECT *, COUNT(v.visit_id)
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;

Finally, MySQL selects the desired columns and renames the COUNT(v.visit_id) column using the AS clause.

SELECT v.customer_id, COUNT(v.visit_id) 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;

Table: Weather

• id is a column with unique values.
• There are no duplicate rows for the same recordDate.
• This table contains information about the temperature on specific dates.

Problem Statement

Write a solution to find the id of all dates where the temperature is higher compared to the previous day (yesterday).

Return

• Return the result table in any order.

Input:

Weather Table:

Output:

Explanation:

• On 2015-01-02, the temperature was higher than the previous day (10 -> 25).
• On 2015-01-04, the temperature was higher than the previous day (20 -> 30).

In the above query, the DATE_ADD(w2.recordDate, INTERVAL 1 DAY) = w1.recordDate condition ensures that the recordDate of w1 matches the day following w2’s recordDate.

SELECT *
FROM Weather w1
JOIN Weather w2
  ON DATE_ADD(w2.recordDate, INTERVAL 1 DAY) = w1.recordDate

Result of the Join:

• when id equals to 2, 25 > 10, so it is selcted.
• when id equals to 3, 20 < 25, so it is not selcted.
• when id equals to 4, 25 > 10, so it is selcted.

Therefore, the result will be:

Example: Relating Employees to Their Managers

Employees Table:

SELECT e1.name AS Employee, e2.name AS Manager
FROM Employees e1
JOIN Employees e2
  ON e1.manager_id = e2.id;

Result of the Join:

Final Result:

Problem Description

Table: Employee

• id is the primary key, ensuring each value is unique.
• Each row represents an employee, including their name, department, and their manager’s ID.
• If managerId is null, the employee does not report to any manager.
• An employee cannot be their own manager.

Task

Write a query to find the managers who have at least five direct reports.

Return the result in any order.

Input:

Employee Table:

Output:

Table: Employee:

Perform a self join, the table becomes:

SELECT *
FROM Employee a
JOIN Employee b 
ON a.managerId = b.id

Table: Slef Joined Result:

Group the self joined result:

SELECT *
FROM Employee a
JOIN Employee b 
ON a.managerId = b.id
GROUP BY b.id

Table: Grouped Result:

Filter grouped result:

SELECT *
FROM Employee a
JOIN Employee b 
ON a.managerId = b.id
GROUP BY b.id
HAVING COUNT(*) >= 5

Table: Filtered Grouped Result:

Select the specified column(s):

SELECT b.name
FROM Employee a
JOIN Employee b 
ON a.managerId = b.id
GROUP BY b.id
HAVING COUNT(*) >= 5

Final Result:

Table: Activity

• The table records user activities for machines on a factory website.
• The combination of (machine_id, process_id, activity_type) is the primary key, ensuring uniqueness in the table.
• machine_id is the ID of the machine.
• process_id is the ID of the process running on the machine with ID machine_id.
• activity_type is an ENUM type with values 'start' and 'end', representing the start and end of a process.
• timestamp is a float value representing the time (in seconds) the event occurred.
• The 'start' timestamp is always earlier than the 'end' timestamp for each (machine_id, process_id) pair.
• It is guaranteed that each (machine_id, process_id) pair has a corresponding 'start' and 'end' timestamp.

Problem:

There are several machines on a factory website, and each machine runs the same number of processes. Your task is to write a SQL query that calculates the average time each machine takes to complete a process.

• The time to complete a process is the difference between the 'end' timestamp and the 'start' timestamp.
• The average time for each machine is calculated by dividing the total time for all processes on that machine by the number of processes.

The result should contain the following columns:

• machine_id — the ID of the machine.
• processing_time — the average processing time, rounded to 3 decimal places.

Input:

Activity table:
|------------|------------|---------------|-----------|
| machine_id | process_id | activity_type | timestamp |
|------------|------------|---------------|-----------|
| 0          | 0          | start         | 0.712     |
| 0          | 0          | end           | 1.520     |
| 0          | 1          | start         | 3.140     |
| 0          | 1          | end           | 4.120     |
| 1          | 0          | start         | 0.550     |
| 1          | 0          | end           | 1.550     |
| 1          | 1          | start         | 0.430     |
| 1          | 1          | end           | 1.420     |
| 2          | 0          | start         | 4.100     |
| 2          | 0          | end           | 4.512     |
| 2          | 1          | start         | 2.500     |
| 2          | 1          | end           | 5.000     |
|------------|------------|---------------|-----------|

Output:

|------------|-----------------|
| machine_id | processing_time |
|------------|-----------------|
| 0          | 0.894           |
| 1          | 0.995           |
| 2          | 1.456           |
|------------|-----------------|

Explanation:

• Machine 0:

  • Process 0: End time 1.520, Start time 0.712 → Time taken = 1.520 - 0.712 = 0.808
  • Process 1: End time 4.120, Start time 3.140 → Time taken = 4.120 - 3.140 = 0.980
  • Average time = (0.808 + 0.980) / 2 = 0.894

• Machine 1:

  • Process 0: End time 1.550, Start time 0.550 → Time taken = 1.550 - 0.550 = 1.000
  • Process 1: End time 1.420, Start time 0.430 → Time taken = 1.420 - 0.430 = 0.990
  • Average time = (1.000 + 0.990) / 2 = 0.995

• Machine 2:

  • Process 0: End time 4.512, Start time 4.100 → Time taken = 4.512 - 4.100 = 0.412
  • Process 1: End time 5.000, Start time 2.500 → Time taken = 5.000 - 2.500 = 2.500
  • Average time = (0.412 + 2.500) / 2 = 1.456

In the following SQL query, the condition a.activity_type = 'start' and b.activity_type = 'end' ensures that the start and end timestamp are correctly matched for the same process on the same machine.

SELECT *
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'  

Result of the Join:

The result is selected from of the joined result:

SELECT machine_id,
       ROUND(AVG(end_time - start_time), 3) AS processing_time
FROM (
    SELECT a.machine_id,
           a.process_id,
           a.timestamp AS start_time,
           b.timestamp AS end_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'
) AS process_times
GROUP BY machine_id;

Final Result:

Table: Students

• student_id is the primary key (unique values) for this table.
• Each row represents a student with their unique ID and name.

Table: Subjects

• subject_name is the primary key (unique values) for this table.
• Each row represents the name of a subject offered in the school.

Table: Examinations

• There is no primary key for this table, and it may contain duplicates.
• Each row indicates that a student with student_id attended the exam for the subject subject_name.
• Every student takes every course from the Subjects table.

Task:

Write a query to find the number of times each student attended each exam, ordered by student_id and subject_name.

Input:

Students Table:

Subjects Table:

Examinations Table:

Output:

Explanation:

The result table contains all students and all subjects.

• Alice attended the Math exam 3 times, the Physics exam 2 times, and the Programming exam 1 time.
• Bob attended the Math exam 1 time, the Programming exam 1 time, and did not attend the Physics exam.
• Alex did not attend any exams.
• John attended the Math exam 1 time, the Physics exam 1 time, and the Programming exam 1 time.

Table: Students

Table: Subjects:

When a CROSS JOIN is performed between the Students and Subjects tables, the result will be:

SELECT *
FROM 
    Students s
CROSS JOIN 
    Subjects sub

Table: Result of Cross Join:

Table: Examniations:

Perform a LEFT JOIN on the cross-joined tables with the Examinations table:

SELECT *
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

Table: Left Join Result:

After grouping the left joined result and count the e.studentId, the table will look like:

SELECT *, COUNT(e.student_id) 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

Table: Grouped Result with Count:

After selecting the specified columns, the result table becomes:

SELECT 
    s.student_id,
    s.student_name,
    sub.subject_name,
    COUNT(e.student_id) 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

Sort the selcted table based on the condition s.student_id and sub.subject_name, return the final result:

Table: Final Result:

Table: Prices

• The primary key for this table is the combination of (product_id, start_date, end_date), ensuring unique periods for each product.
• Each row represents the price of a product for a specific period, from start_date to end_date.
• For each product, no two periods will overlap.

Table: UnitsSold

• This table may contain duplicate rows.
• Each row represents the date, number of units, and product_id for a product sold on that date.

Problem:

Write a SQL query to find the average selling price for each product. The average_price should be rounded to two decimal places. If a product does not have any sold units, its average selling price should be considered 0.

Return the result table in any order.

Input:

Prices table:

UnitsSold table:

Output:

Explanation:

• For product 1:

  • From 2019-02-17 to 2019-02-28, 100 units were sold at a price of 5.
  • From 2019-03-01 to 2019-03-22, 15 units were sold at a price of 20.
  • Average price is calculated based on the total price and total units sold.

• For product 2:

  • From 2019-02-01 to 2019-02-20, 200 units were sold at a price of 15.
  • From 2019-02-21 to 2019-03-31, 30 units were sold at a price of 30.
  • Average price is calculated based on the total price and total units sold.

MySQL Query Walkthrough:

Table: Prices

Table: UnitsSold

Join the two tables based on the product_id and the purchase_date, where the purchase_date falls between the start_date and the end_date:

SELECT * 
FROM Prices AS p
LEFT JOIN UnitsSold AS u
ON p.product_id = u.product_id AND purchase_date BETWEEN start_date AND end_date;

Group the joined result and then use aggregation functions to calculate the average price:

SELECT 
    *,
    ROUND(IFNULL(SUM(price * units) / SUM(units), 0), 2) AS average_price
FROM Prices AS p
LEFT JOIN UnitsSold AS u
ON p.product_id = u.product_id AND purchase_date BETWEEN start_date AND end_date
GROUP BY 1;

Select the desired columns:

SELECT 
    p.product_id,
    ROUND(IFNULL(SUM(price * units) / SUM(units), 0), 2) AS average_price
FROM Prices AS p
LEFT JOIN UnitsSold AS u
ON p.product_id = u.product_id AND purchase_date BETWEEN start_date AND end_date
GROUP BY 1;

# Wrong Answer
SELECT
    p.product_id,
    ROUND(IFNULL(SUM(price * units) / SUM(units), 0), 2) AS average_price
FROM
    Prices AS p
LEFT JOIN UnitsSold AS u ON p.product_id = u.product_id
WHERE purchase_date BETWEEN start_date AND end_date
GROUP BY 1;

Join the two tables based on the condition p.product_id = u.product_id:

SELECT *
FROM Prices AS p
LEFT JOIN UnitsSold AS u ON p.product_id = u.product_id;

Filter out the rows that do not fall between start_date and end_date:

SELECT *
FROM Prices AS p
LEFT JOIN UnitsSold AS u ON p.product_id = u.product_id
WHERE purchase_date BETWEEN start_date AND end_date;

Group the filtered result and then use aggregation functions to calculate average price:

SELECT 
    *,
    ROUND(IFNULL(SUM(price * units) / SUM(units), 0), 2) AS average_price
FROM Prices AS p
LEFT JOIN UnitsSold AS u ON p.product_id = u.product_id
WHERE purchase_date BETWEEN start_date AND end_date
GROUP BY 1;

Select the desired columns:

SELECT 
    p.product_id,
    ROUND(IFNULL(SUM(price * units) / SUM(units), 0), 2) AS average_price
FROM Prices AS p
LEFT JOIN UnitsSold AS u ON p.product_id = u.product_id
WHERE purchase_date BETWEEN start_date AND end_date
GROUP BY 1;

The correct answer is:

Summary: The LEFT JOIN operation will return NULL results for rows without without matching records in the UnitsSold table. In the condition p.product_id = u.product_id AND purchase_date BETWEEN start_date AND end_date, the date range check is applied during the join operation, while WHERE purchase_date BETWEEN start_date AND end_date is applied after the join operation. The WHERE clause exlcudes rows with NULL values, which means products with no matching sales records (i.e., where purchase_date doesn’t match) will be excluded from the result. When grouping the result, no rows containing NULL values remain, leading to an incorrect result (missing product_id = 3 with average_price = 0).

Table: Users

• user_id is the primary key (a unique identifier) for this table.
• Each row in this table represents a user, with their unique ID and name.

Table: Register

• (contest_id, user_id) is the primary key (a unique combination of columns) for this table.
• Each row in this table represents the registration of a user in a specific contest.

Task

Write a solution to calculate the percentage of users registered for each contest, rounded to two decimal places.

Return the result table sorted by percentage in descending order. If there is a tie in percentage, order by contest_id in ascending order.

Input:

Users table:

Register table:

Output:

Explanation:

• Contests 208, 209, and 210 had 100% user registration. The results are sorted by contest_id in ascending order.
• Contest 215 had a registration rate of 66.67%, as Alice and Alex registered, out of a total of three users.
• Contest 207 had a registration rate of 33.33%, as only Bob registered, out of a total of three users.

$$ \text{percentage} = \frac{\text{number of users attended in the contest}}{\text{total number of users}} \times 100 \% $$

• Compute the total number of users: SELECT COUNT(user_id) FROM Users
• Compute the number of users attended in the contest: Group the table based on the column contest_id, then count the number of users in each group.

Table: Queries

• This table may contain duplicate rows.
• It contains information collected from various queries executed on a database.
• The position column has a value ranging from 1 to 500.
• The rating column has a value between 1 and 5. Queries with a rating less than 3 are considered poor queries.

Definitions:

• Query quality: The average of the ratio between the query’s rating and its position.
• Poor query percentage: The percentage of queries with a rating less than 3.

Objective: Write a solution to find:

• The query_name,
• The quality of each query (rounded to 2 decimal places),
• The poor_query_percentage for each query (rounded to 2 decimal places).

Input: Queries table:

Output:

Explanation:

• Dog queries:

  • Quality:
    $$ \left( \frac{5}{1} + \frac{5}{2} + \frac{1}{200} \right) / 3 = 2.50 $$
  • Poor query percentage:
    $$ \frac{1}{3} \times 100 = 33.33 $$

• Cat queries:

  • Quality:
    $$ \left( \frac{2}{5} + \frac{3}{3} + \frac{4}{7} \right) / 3 = 0.66 $$
  • Poor query percentage:
    $$ \frac{1}{3} \times 100 = 33.33 $$

Table: Transactions

• id is the primary key of this table.
• The table stores information about incoming transactions.
• The state column is an enum with values [“approved”, “declined”].

Task:

For each month and country, find the following information:

• The total number of transactions (trans_count).
• The total amount of all transactions (trans_total_amount).
• The number of approved transactions (approved_count).
• The total amount of approved transactions (approved_total_amount).

The results should be returned in any order.

Example Input:

Example Output:

• The DATE_FORMAT function in SQL is used to format a date into a specific string format. For example, DATE_FORMAT('2024-12-19', '%Y-%m') will output '2024-12', extracting the year and month from the date.
• The IF function in SQL returns one value if the condition is true and another value if the condition is false. For example, IF(amount > 1000, 1, 0) will return 1 if the amount is greater than 1000, otherwise, it will return 0.
• The CASE...WHEN...THEN...ELSE...END statement is similar to the switch statement in other programming languages like Java. It works like the IF function, but with the ability to handle multiple conditions. It checks each condition in sequence and returns the corresponding result for the first true condition.

Table: Delivery

• delivery_id is the unique identifier for each delivery.
• The table contains information about food deliveries, where customers place orders on a specific date and specify a preferred delivery date (either on the same day or later).
• If the customer_pref_delivery_date is the same as the order_date, the order is considered immediate; otherwise, it is scheduled.
• The first order of a customer is defined as the one with the earliest order_date. Each customer has exactly one first order.

Task:

Find the percentage of immediate orders among the first orders of all customers, rounded to two decimal places.

The result should follow the format shown below:

Example Input:

Example Output:

Explanation:

• Customer 1 has their first order with delivery_id 1, which is scheduled.
• Customer 2 has their first order with delivery_id 2, which is immediate.
• Customer 3 has their first order with delivery_id 5, which is scheduled.
• Customer 4 has their first order with delivery_id 7, which is immediate.

Hence, 50% of the first orders are immediate.

Table: Activity

• The combination of (player_id, event_date) is the primary key of this table, ensuring each player can only log in once per day.
• This table tracks the activity of players in games, where each record represents a player’s login and the number of games played (which could be zero) before logging out on a specific day using a particular device.

Task:

Calculate the fraction of players who logged in again on the day after their first login date. The result should be rounded to two decimal places.

In other words, identify the players who logged in on consecutive days starting from their first login date, then divide that count by the total number of players.

Example Input:

Example Output:

Explanation:

• Player 1 logged in on 2016-03-01 and 2016-03-02, meaning they logged in for at least two consecutive days, starting from their first login.
• Player 2 did not log in again on the day after their first login.
• Player 3 did not log in on consecutive days, as there was a gap between their logins.

Thus, only player 1 satisfies the condition, and the fraction is calculated as 1/3 = 0.33.

• The DATEDIFF function in MySQL calculates the difference in days between two dates. For example, DATEDIFF('2024-12-30', '2024-12-25') will output 5, indicating there are 5 days between the two dates.
• The PARTITION BY clause is used in window functions to divide the result ser into partitions (groups) based on a specified column. It applies window functions, such as RANK(), SUM(), ROW_NUMBER(), and others, to each partition. This works similarly to GROUP BY, but unlike GROUP BY, the PARTITION BY clause allows retaining the row-level data while applying the window functions.

Input Table: employees

SELECT 
    department_id, 
    employee_id, 
    salary, 
    RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) AS salary_rank
FROM employees;

Output (Result of Query):

Table: Activity

• The activity_type column is an ENUM with values: ‘open_session’, ’end_session’, ‘scroll_down’, ‘send_message’.
• This table logs user activities for a social media website, where each session is linked to exactly one user.
• The table may contain duplicate rows.

Task:

You need to find the count of unique active users per day for the 30-day period ending on 2019-07-27, inclusive. A user is considered active on a particular day if they perform at least one activity on that day.

Example:

Input: Activity table

Output:

Explanation:

• Only the days with active users (those who performed at least one activity) are included in the output.
• For 2019-07-20, users 1 and 2 were active, and for 2019-07-21, users 2 and 3 were active.

In MySQL, both DATEDIFF and DATE_SUB are used for date calculations. DATEDIFF calculates the difference between two dates, returning the result in days. On the other hand, DATE_SUB is used to subtract a specified time interval (such as days, months, or years) from a given date.

SELECT DATEDIFF('2023-12-31', '2023-01-01'); 
-- Result: 364

SELECT DATE_SUB('2023-12-31', INTERVAL 30 DAY); 
-- Result: '2023-12-01'

SELECT DATE_SUB('2023-12-31', INTERVAL 2 MONTH);
-- Result: '2023-10-31'

Table: Sales

• The combination of sale_id and year is the primary key of this table, ensuring each record is unique for a given sale.
• product_id is a foreign key referencing the Product table.
• Each row in this table represents a sale of a specific product (product_id) in a given year.
• The price refers to the price per unit of the product.

Table: Product

• product_id is the primary key of this table, ensuring unique product identifiers.
• Each row represents the name of a product associated with the corresponding product_id.

Task: Write a query to select the product_id, year, quantity, and price for the first year in which each product was sold.

Return the result in any order.

Example:

Input: Sales table

Product table:

Output:

Explanation:

• For product_id = 100, the first sale occurred in 2008 with a quantity of 10 and a price of 5000.
• For product_id = 200, the first sale occurred in 2011 with a quantity of 15 and a price of 9000.

The query should return the first sale year for each product, along with the corresponding quantity and price.

Table: MyNumbers

This table may contain duplicates (i.e., there is no primary key in the SQL table). Each row contains an integer.

Problem Description

A single number is a number that appears only once in the MyNumbers table.

The task is to find the largest single number. If there is no single number, return null.

Example 1:

Input: MyNumbers table:

Output:

Explanation:
The single numbers are 1, 4, 5, and 6. Since 6 is the largest single number, we return it.

Example 2:

Input: MyNumbers table:

Output:

Explanation:
There are no single numbers in the input table, so we return null.

Table: Employees

• employee_id is the unique identifier for each employee in the table.
• This table stores information about employees and the ID of the manager they report to. Some employees may not report to anyone (reports_to is null).
• A manager is defined as an employee who has at least one other employee reporting to them.

Problem Description

You are required to write a solution that reports the following information for each manager:

• The manager’s employee_id and name.
• The number of employees directly reporting to the manager.
• The average age of these employees, rounded to the nearest integer.

The results should be ordered by employee_id.

Example 1:

Input:

Employees table:

Output:

Explanation:

• Hercy manages 2 employees (Alice and Bob).
• The average age of Alice and Bob is (41 + 36) / 2 = 38.5, which rounds to 39.

Example 2:

Input:

Employees table:

Output:

Explanation:

• Michael manages Alice and Bob. The average age of Alice and Bob is (38 + 42) / 2 = 40.
• Alice manages Charlie and David, with an average age of (34 + 40) / 2 = 37.
• Bob manages Eve, with an average age of 37.

Table: Employee

• The combination of (employee_id, department_id) is the primary key for this table, meaning each employee can belong to multiple departments, but each (employee_id, department_id) pair is unique.
• employee_id: The ID of the employee.
• department_id: The ID of the department to which the employee belongs.
• primary_flag: A flag indicating whether the department is the primary department for the employee. It can be one of the following:
  • 'Y': The department is the primary department.
  • 'N': The department is not the primary department.

Problem Description

Employees may belong to multiple departments, and when they do, they must designate one department as their primary. If an employee belongs to only one department, the primary_flag for that department will be 'N'.

You are tasked with reporting the primary department for each employee. If an employee has only one department, report that department as their primary.

Input:

Employee table:

Output:

Explanation:

• For employee 1, their only department is department 1, so it is reported as their primary department.
• Employee 2 belongs to two departments (1 and 2). The primary_flag for department 1 is 'Y', so department 1 is their primary department.
• Employee 3 only belongs to department 3, so it is reported as their primary department.
• Employee 4 belongs to three departments (2, 3, and 4). The primary_flag for department 3 is 'Y', so department 3 is reported as their primary department.

The UNION operator is used to combine the results of two or more SELECT queries into a single result set. By default, UNION removes duplicate rows, ensuring that the final result contains only distinct records. In contrast, UNION ALL includes all rows, even if they are duplicates. It’s important to note that each SELECT statement involved in a UNION operation must contain the same number of columns, and the corresponding columns must have compatable data types.

Table: Triangle

• Primary Key: (x, y, z)
  Each row in this table represents the lengths of three line segments.

Task:
Determine whether the three line segments (x, y, z) from each row can form a triangle.
Return the result table in any order, including a column that specifies if the segments form a triangle.

Triangle Formation Rule:
Three segments can form a triangle if and only if the following conditions are met:

1. $x + y > z$
2. $x + z > y$
3. $y + z > x$

Example:

Input:

**Triangle table: **

Output:

Table: Logs

• id is the primary key for this table.
• The id column is an auto-increment column that starts from 1.

Problem Statement:

Find all numbers (num) that appear at least three times consecutively in the table.

Return the result table in any order.

Example:

Input:

Logs table:

Output:

Explanation:

The number 1 is the only value that appears at least three times consecutively in the table.

Table: Products

• (product_id, change_date) is the primary key (a combination of columns with unique values) of this table.
• Each row indicates that the price of a product was changed to a new price on a specific date.

Problem Statement

Write a query to find the prices of all products on 2019-08-16. Assume the price of all products before any change is 10.

Return the result in any order.

Example:

Input:

Products table:

Output:

Table: Queue

• The person_id column contains unique values.
• This table holds information about people waiting to board a bus.
• The person_id and turn columns contain values from 1 to n, where n is the number of rows in the table.
• The turn column determines the order in which people will board the bus. A turn value of 1 indicates the first person to board, and turn = n indicates the last person to board.
• The weight column represents the person’s weight in kilograms.

The bus has a weight limit of 1000 kilograms, so some people may not be able to board if the total weight exceeds this limit. The goal is to identify the name of the last person who can board the bus without exceeding the weight limit.

• Only one person can board the bus at any given time, based on their turn.

Input:

Queue table:

Output:

Explanation:

The following table shows the order in which people board the bus, ordered by the turn column for simplicity:

John Cena is the last person who can board the bus without exceeding the weight limit of 1000 kilograms.

Table: Accounts

• The account_id column is the primary key, meaning it contains unique values for each row.
• Each row represents the monthly income of a specific bank account.

Task:

Write a solution to calculate the number of bank accounts in each salary category. The salary categories are defined as:

• “Low Salary”: Salaries strictly less than $20,000.
• “Average Salary”: Salaries in the inclusive range [$20,000, $50,000].
• “High Salary”: Salaries strictly greater than $50,000.

The result table should contain all three categories. If no accounts fall into a category, return 0 for that category.

The result can be returned in any order.

Input:

Accounts table:

Output:

Explanation:

• Low Salary: Account 2 has an income of $12,747, which is below $20,000.
• Average Salary: There are no accounts with an income between $20,000 and $50,000.
• High Salary: Accounts 3, 6, and 8 have incomes above $50,000.

Table: Seat

• id is the primary key for this table.
• Each row represents a student’s name and their seat ID. The id starts from 1 and increases consecutively.

Problem Description:

Write a query to swap the seat IDs of every two consecutive students in the Seat table. If the number of students is odd, the last student’s seat ID should remain unchanged.

The result should be ordered by the id column in ascending order.

Input:

Seat table:

Output:

Explanation:

• The students Abbot and Doris swap places, as well as Emerson and Green.
• Since there are an odd number of students, Jeames’s seat remains unchanged.

Table: Movies

movie_id is the primary key (column with unique values) for this table. title is the name of the movie.

Table: Users

user_id is the primary key (column with unique values) for this table. The column ’name’ has unique values.

Table: MovieRating

(movie_id, user_id) is the primary key (column with unique values) for this table. This table contains the rating of a movie by a user in their review. created_at is the user’s review date.

Problem:

1. Find the name of the user who has rated the greatest number of movies. In case of a tie, return the lexicographically smaller user name.
2. Find the movie name with the highest average rating in February 2020. In case of a tie, return the lexicographically smaller movie name.

Result format:

The result should contain two columns:

1. results – containing the user name with the greatest number of movie ratings.
2. results – containing the movie name with the highest average rating in February 2020.

Input:

Movies table:

Users table:

MovieRating table:

Output:

Explanation:

• Daniel and Monica have rated 3 movies (“Avengers”, “Frozen 2” and “Joker”), but Daniel is lexicographically smaller.
• “Frozen 2” and “Joker” both have an average rating of 3.5 in February, but “Frozen 2” is lexicographically smaller.