Intermediate SQL katas

Photo by Nick Morrison on Unsplash

If you have been out of touch with your SQL chops and would like a little practice or challenge, here are some queries you could use to refresh your skills.

There are five questions with sample DDL to go with each. You could run this on your local, container or cloud database or use an online fiddler like https://sqlfiddle.com/ or https://www.db-fiddle.com/.

Possible solutions are provided at the end based on MySQL 9. Of course you could also use Chat GPT or whatever for hints and answers. You can get the best value out of these by spending some time with the problems before jumping to the answers.

Question 1

Consider a table named “Orders” with the following columns: OrderID, OrderDate, CustomerID.

Write a SQL query to find the customers who have placed orders on consecutive days.

Setup

CREATE TABLE Orders (
  OrderId INT AUTO_INCREMENT KEY,
  OrderDate DATE,
  CustomerId INT
);

INSERT INTO Orders(OrderDate, CustomerId) 
VALUES 
("2018-10-1", 300),
("2018-10-1", 400),
("2018-10-1", 500),
("2018-10-2", 300),
("2018-10-2", 500),
("2018-11-2", 300),
("2018-11-2", 400),
("2018-10-3", 300),
("2018-10-3", 400),
("2018-10-3", 500),
("2018-10-4", 400),
("2018-10-4", 500),
("2018-10-5", 400),
("2018-10-6", 300);

Question 2

You have three tables: “Employees,” “Departments,” and “Salaries.” The “Employees” table has the following columns: EmployeeID, EmployeeName, DepartmentID. The “Departments” table has the following columns: DepartmentID, DepartmentName. The “Salaries” table has the following columns: EmployeeID, Salary, EffectiveDate.

Write a SQL query to retrieve the employee who has had the highest salary increase within the last year, along with their name, department, and the percentage increase.

Setup

CREATE TABLE Departments (
  DepartmentID INT KEY,
  DepartmentName VARCHAR(100)
);

CREATE TABLE Employees (
  EmployeeID INT KEY,
  EmployeeName VARCHAR(100),
  DepartmentID INT
);

CREATE TABLE Salaries (
  EmployeeID INT,
  Salary DOUBLE,
  EffectiveDate DATE
);

INSERT INTO Departments(DepartmentID, DepartmentName) 
VALUES 
(1, 'Accounting'),
(2, 'IT'),
(3, 'Marketing');

INSERT INTO Employees(EmployeeID, EmployeeName, DepartmentID) 
VALUES 
(101, 'Abdul', 1),
(102, 'Bob', 1),
(103, 'Chris', 1),
(104, 'Divya', 2),
(105, 'Emily', 2),
(106, 'Farida', 2),
(107, 'Ganesh', 3),
(108, 'Hemant', 3);

INSERT INTO Salaries(EmployeeID, Salary, EffectiveDate) 
VALUES 
(101, 1000, '2012-10-01'),
(101, 1500, '2013-10-01'),
(101, 1900, '2014-10-01'),
(102, 1500, '2012-10-01'),
(102, 1800, '2013-10-01'),
(102, 2200, '2014-10-01'),
(103, 1200, '2012-10-01'),
(103, 1500, '2013-10-01'),
(103, 1800, '2014-10-01'),
(104, 1300, '2012-10-01'),
(104, 1800, '2013-10-01'),
(104, 2500, '2014-10-01'),
(105, 1000, '2012-10-01'),
(105, 1500, '2013-10-01'),
(105, 1900, '2014-10-01'),
(106, 1000, '2012-10-01'),
(106, 1300, '2013-10-01'),
(106, 1700, '2014-10-01'),
(107, 2000, '2012-10-01'),
(107, 2200, '2013-10-01'),
(107, 2500, '2014-10-01'),
(108, 500, '2012-10-01'),
(108, 1100, '2013-10-01'),
(108, 1800, '2014-10-01');

Question 3

You have a table named “Logs” with the following columns: LogID, LogTime, UserID.

Write a SQL query to find the top 5 users who have logged in the most consecutive days, along with the number of consecutive days.

Setup

CREATE TABLE Logs (
  LogID INT AUTO_INCREMENT KEY,
  LogTime DATETIME,
  UserID INT
);

INSERT INTO Logs (LogTime, UserID)
VALUES 
('2024-10-01 09:00:00', 1),
('2024-10-02 09:15:00', 1),
('2024-10-03 09:20:00', 1),
('2024-10-05 09:30:00', 1),
('2024-10-06 09:45:00', 1),
('2024-10-01 10:00:00', 2),
('2024-10-02 10:05:00', 2),
('2024-10-03 10:10:00', 2),
('2024-10-04 10:15:00', 2),
('2024-10-05 10:20:00', 2),
('2024-10-06 10:25:00', 2),
('2024-10-01 11:00:00', 3),
('2024-10-02 11:05:00', 3),
('2024-10-04 11:10:00', 3),
('2024-10-05 11:15:00', 3),
('2024-10-07 11:20:00', 3),
('2024-10-01 12:00:00', 4),
('2024-10-02 12:05:00', 4),
('2024-10-03 12:10:00', 4),
('2024-10-02 13:00:00', 5),
('2024-10-03 13:05:00', 5),
('2024-10-04 13:10:00', 5),
('2024-10-05 13:15:00', 5),
('2024-10-07 13:20:00', 5),
('2024-10-03 14:00:00', 6),
('2024-10-04 14:05:00', 6),
('2024-10-06 14:10:00', 6),
('2024-10-01 15:00:00', 7),
('2024-10-02 15:05:00', 7),
('2024-10-03 15:10:00', 7),
('2024-10-05 15:15:00', 7),
('2024-10-06 15:20:00', 7),
('2024-10-07 12:22:00', 7);

Question 4

Consider a table named “Transactions” with the following columns: TransactionID, TransactionDate, Amount, UserID.

Write a SQL query to calculate the average transaction amount for each user, including users who have no transactions, and display the result as zero for those users.

Setup

CREATE TABLE Transactions (
    TransactionID INT PRIMARY KEY,
    TransactionDate DATETIME,
    Amount DECIMAL(10, 2),
    UserID INT
);
CREATE TABLE Users (
    UserID INT PRIMARY KEY,
    UserName VARCHAR(100)
);

INSERT INTO Transactions (TransactionID, TransactionDate, Amount, UserID)
VALUES
(1, '2024-01-01 10:30:00', 50.00, 1),
(2, '2024-01-02 11:00:00', 75.00, 2),
(3, '2024-01-02 12:15:00', 100.00, 1),
(4, '2024-01-03 09:45:00', 25.00, 3),
(5, '2024-01-04 14:30:00', 60.00, 2),
(6, '2024-01-05 15:20:00', 90.00, 4),
(7, '2024-01-06 08:00:00', 120.00, 3),
(8, '2024-01-07 09:10:00', 150.00, 1);

INSERT INTO Users (UserID, UserName)
VALUES
(1, 'Alice'),
(2, 'Bob'),
(3, 'Charlie'),
(4, 'David'),
(5, 'Eve');  -- User with no transactions

Question 5

You have two tables: “Customers” and “Purchases.” The “Customers” table has the following columns: CustomerID, CustomerName. The “Purchases” table has the following columns: PurchaseID, PurchaseDate, CustomerID, ProductID.

Write a SQL query to find the customers who have purchased all products.

Setup

CREATE TABLE Customers (
    CustomerID INT PRIMARY KEY,
    CustomerName VARCHAR(100)
);

CREATE TABLE Purchases (
    PurchaseID INT PRIMARY KEY,
    PurchaseDate DATE,
    CustomerID INT,
    ProductID INT,
    FOREIGN KEY (CustomerID) REFERENCES Customers(CustomerID)
);

INSERT INTO Customers (CustomerID, CustomerName)
VALUES
(1, 'Adam'),
(2, 'Bob'),
(3, 'Charlie'),
(4, 'Divya');

INSERT INTO Purchases (PurchaseID, PurchaseDate, CustomerID, ProductID)
VALUES
(1, '2024-01-01', 1, 101),
(2, '2024-01-02', 1, 102),
(3, '2024-01-03', 2, 101),
(4, '2024-01-04', 2, 102),
(5, '2024-01-05', 3, 101),
(6, '2024-01-06', 3, 102),
(7, '2024-01-07', 3, 103),
(8, '2024-01-08', 4, 101),
(9, '2024-01-09', 4, 102),
(10, '2024-01-10', 4, 103);

Hints

Question 1 — Use the LAG or LEAD window function on OrderDate to get the previous or next date.

Question 2 — Formula for percentage increase is (current salary — previous salary) / (previous salary).

Question 3 — Calculate the gaps (or streaks) between consecutive days. Then use the SUM window function with the gaps (or streaks) to create groups of consecutive logins.

Question 4 — Use AVG to calculate averages but find a workaround to handle null values.

Question 5 — Take the unique count of products and compare with the unique count of products purchased by a customer. Assume that the products in the Purchases table have all products.

Solution for Question 1

WITH ConsecutiveDays AS (
SELECT OrderId, OrderDate, CustomerId, 
LEAD(OrderDate) OVER (PARTITION BY CustomerId ORDER BY OrderDate) AS NextOrderDate
FROM Orders 
) 
SELECT CustomerId, OrderDate, NextOrderDate 
FROM ConsecutiveDays 
WHERE NextOrderDate = OrderDate + INTERVAL 1 DAY

Notes

The NextOrderDate is determined by the LEAD function for each customer. The partition is ordered by OrderDate so that it takes the next order date and not the next row in the table.

A common table expression (CTE) holds the intermediate result for comparing the date difference.

OrderDate + INTERVAL 1 DAY is syntactic sugar for DATE_ADD(OrderDate, INTERVAL 1 DAY)

We can use the LAG function in a similar manner to get the PreviousOrderDate and use it in the CTE.

Solution for Question 2

WITH SalaryWithLag AS (
SELECT 
   EmployeeID, 
   Salary as CurrentSalary, 
   EffectiveDate, 
   LAG(Salary) OVER (PARTITION BY EmployeeID ORDER BY EffectiveDate) AS PreviousSalary
  FROM 
   Salaries
  WHERE 
   EffectiveDate >= DATE_SUB('2014-10-01', INTERVAL 1 YEAR)
)
, SalaryChanges AS (
-- remove rows that don't have a previous salary
  SELECT 
   EmployeeID,
    CurrentSalary,
    PreviousSalary,
    ((CurrentSalary - PreviousSalary) / PreviousSalary) * 100 AS PercentageIncrease
  FROM 
    SalaryWithLag
  WHERE 
    PreviousSalary IS NOT NULL
)
SELECT EmployeeName, DepartmentName, PercentageIncrease 
FROM SalaryChanges S
JOIN Employees E ON E.EmployeeID = S.EmployeeID
JOIN Departments D ON D.DepartmentID = E.DepartmentID
ORDER BY PercentageIncrease DESC
LIMIT 1;

or

WITH SalaryChanges AS (
    -- Get the salary one year ago and the current salary
    SELECT 
        s1.EmployeeID,
        s1.Salary AS CurrentSalary,
        s2.Salary AS SalaryOneYearAgo,
        ((s1.Salary - s2.Salary) / s2.Salary) * 100 AS PercentageIncrease
    FROM 
        Salaries s1
    JOIN 
        Salaries s2 
        ON s1.EmployeeID = s2.EmployeeID
    WHERE 
        s1.EffectiveDate = (SELECT MAX(EffectiveDate) FROM Salaries WHERE EmployeeID = s1.EmployeeID)
        AND s2.EffectiveDate = (SELECT MAX(EffectiveDate) FROM Salaries WHERE EmployeeID = s2.EmployeeID AND EffectiveDate <= DATE_SUB(s1.EffectiveDate, INTERVAL 1 YEAR))
)
-- Select the employee with the highest salary increase
SELECT e.EmployeeName, d.DepartmentName, sc.PercentageIncrease
FROM SalaryChanges sc
JOIN Employees e ON sc.EmployeeID = e.EmployeeID
JOIN Departments d ON e.DepartmentID = d.DepartmentID
ORDER BY sc.PercentageIncrease DESC
LIMIT 1;

Notes

In the first solution, the EffectiveDate can be adjusted for different time ranges for eg: 2 years instead of previous year. The interval adjustment works differently for the second query and gives different results based on what may be considered as the previous salary. For eg: with 2 years as the interval, the first solution compares increases every year in the last 2 years whereas the second solution makes the comparison of current salary with the one exactly two years ago.

The first solution could be made generic by using CURDATE() instead of ‘2014–10–01’. It was hardcoded to work with the static data.

Solution for Question 3

WITH ConsecutiveLogins AS (
    -- Calculate the difference in days between the current login and the previous one
    SELECT 
        UserID,
        LogTime,
        DATE(LogTime) AS LogDate,
        DATE(LAG(LogTime) OVER (PARTITION BY UserID ORDER BY LogTime)) AS PrevLogDate,
        -- Detects the break in consecutive dates by calculating the gap
        CASE 
            WHEN DATE(LogTime) = DATE(LAG(LogTime) OVER (PARTITION BY UserID ORDER BY LogTime)) + INTERVAL 1 DAY THEN 0
            ELSE 1
        END AS DayGap
    FROM Logs
),
GroupedLogins AS (
    -- Group consecutive logins by cumulative sum of DayGap, effectively identifying consecutive sequences
    SELECT 
        UserID,
        LogDate,
        SUM(DayGap) OVER (PARTITION BY UserID ORDER BY LogDate) AS GroupID,
        DayGap
    FROM ConsecutiveLogins
),
ConsecutiveDaysCount AS (
    -- Count consecutive days for each group of consecutive logins
    SELECT 
        UserID,
        COUNT(*) AS ConsecutiveDays
    FROM GroupedLogins
    GROUP BY UserID, GroupID
)
-- Select the top 5 users with the highest number of consecutive days
SELECT 
    UserID,
    MAX(ConsecutiveDays) AS MaxConsecutiveDays
FROM ConsecutiveDaysCount
GROUP BY UserID
ORDER BY MaxConsecutiveDays DESC
LIMIT 5;

Notes

The LAG window function in the first CTE will create a GroupID starting with 1. If we use the LEAD window function instead, it will create groups that start with 0. Both result in the same output.

To clearly understand how this works, execute each CTE in order and notice how the DayGap is used to create the GroupID. This solution uses breaks in consecutive days to create groups but it can also be done by grouping streaks.

Solution for Question 4

WITH Totals AS (
  SELECT Users.UserId, SUM(Amount) AS Total, COUNT(TransactionID) AS Number
  FROM Transactions RIGHT JOIN Users ON Transactions.UserID = Users.UserID
  GROUP BY Users.UserID
) SELECT UserId, 
 CASE 
     WHEN Number>0 THEN Total/Number
     ELSE 0 
    END as AverageAmount
  FROM Totals;

or

SELECT 
    u.UserID,
    u.UserName,
    IFNULL(AVG(t.Amount), 0) AS AverageTransactionAmount
FROM 
    Users u
LEFT JOIN 
    Transactions t ON u.UserID = t.UserID
GROUP BY 
    u.UserID, u.UserName;

Notes

The IFNULL function in the second solution provides a more readable query but it is also possible to handle it with a CASE expression.

Solution for Question 5

SELECT 
 Customers.CustomerID, 
    CustomerName,
    COUNT(DISTINCT ProductID) AS ProductsPurchased 
   FROM Purchases 
   JOIN Customers ON Purchases.CustomerID = Customers.CustomerID
GROUP BY CustomerID
HAVING ProductsPurchased = (SELECT COUNT(DISTINCT(ProductID)) FROM Purchases);

Notes

The HAVING expression is used with GROUP BY which is how we can add a filtering condition when the WHERE expression cannot be used. The customers are grouped by the unique count of purchases and this is filtered by the total number of unique products that are possible.

You can build on top of this using a separate products table which lists all products since there might be products that have not been purchased by any customer.