SQL Interview Q25 (MySQL)

Fork
/*
Amazon Interview Question

Find Customers who placed more than 50% of their Orders in the Last Month
*/

-- Schema Setup
CREATE TABLE Orders (
    OrderID INT PRIMARY KEY,
    CustomerID VARCHAR(10),
    EmployeeID INT,
    OrderDate DATETIME,
    RequiredDate DATETIME,
    ShippedDate DATETIME,
    ShipVia INT,
    Freight DECIMAL(10,2),
    ShipName VARCHAR(255)
);

CREATE TABLE OrderDetails (
    OrderID INT,
    ProductID INT,
    UnitPrice DECIMAL(10,2),
    Quantity INT,
    Discount DECIMAL(5,2),
    PRIMARY KEY (OrderID, ProductID),
    FOREIGN KEY (OrderID) REFERENCES Orders(OrderID)
);

INSERT INTO Orders VALUES
(10248, 'VINET', 5, '2024-07-04', '2024-08-01', '2024-07-16', 3, 32.38, 'Vins et alcools Chevalier'),
(10249, 'TOMSP', 6, '2024-07-05', '2024-08-16', '2024-07-10', 1, 11.61, 'Toms Spezialitäten'),
(10250, 'HANAR', 4, '2024-07-08', '2024-08-05', '2024-07-12', 2, 65.83, 'Hanari Carnes'),
(10251, 'VICTE', 3, '2024-07-08', '2024-08-05', '2024-07-15', 1, 41.34, 'Victuailles en stock'),
(10252, 'SUPRD', 4, '2024-07-09', '2024-08-06', '2024-07-11', 2, 51.30, 'Suprêmes délices'),
(10253, 'HANAR', 3, '2024-07-10', '2024-07-24', '2024-07-16', 2, 58.17, 'Hanari Carnes'),
(10254, 'CHOPS', 5, '2024-07-11', '2024-08-08', '2024-07-23', 2, 22.98, 'Chop-suey Chinese'),
(10255, 'RICSU', 9, '2024-07-12', '2024-08-09', '2024-07-15', 3, 148.33, 'Richter Supermarkt'),
(10256, 'VINET', 5, '2024-08-01', '2024-08-29', '2024-08-15', 3, 45.00, 'Vins et alcools Chevalier'),
(10257, 'TOMSP', 6, '2024-08-02', '2024-08-30', '2024-08-10', 1, 22.50, 'Toms Spezialitäten'),
(10258, 'HANAR', 4, '2024-08-03', '2024-08-31', '2024-08-12', 2, 70.00, 'Hanari Carnes'),
(10259, 'VICTE', 3, '2024-08-04', '2024-09-01', '2024-08-15', 1, 50.00, 'Victuailles en stock'),
(10260, 'SUPRD', 4, '2024-08-05', '2024-09-02', '2024-08-11', 2, 60.00, 'Suprêmes délices'),
(10262, 'CHOPS', 5, '2024-08-07', '2024-09-04', '2024-08-23', 2, 30.00, 'Chop-suey Chinese'),
(10263, 'RICSU', 9, '2024-08-08', '2024-09-05', '2024-08-15', 3, 150.00, 'Richter Supermarkt'),
(10265, 'TOMSP', 6, '2024-09-02', '2024-09-30', '2024-09-10', 1, 20.00, 'Toms Spezialitäten'),
(10266, 'HANAR', 4, '2024-09-03', '2024-10-01', '2024-09-12', 2, 65.00, 'Hanari Carnes'),
(10267, 'VICTE', 3, '2024-09-04', '2024-10-02', '2024-09-15', 1, 45.00, 'Victuailles en stock'),
(10268, 'SUPRD', 4, '2024-09-05', '2024-10-03', '2024-09-11', 2, 55.00, 'Suprêmes délices'),
(10269, 'HANAR', 3, '2024-09-06', '2024-09-20', '2024-09-16', 2, 50.00, 'Hanari Carnes'),
(10270, 'CHOPS', 5, '2024-09-07', '2024-10-05', '2024-09-23', 2, 25.00, 'Chop-suey Chinese'),
(10271, 'RICSU', 9, '2024-09-08', '2024-10-06', '2024-09-15', 3, 140.00, 'Richter Supermarkt'),
(10272, 'VINET', 5, '2024-10-01', '2024-10-29', '2024-10-15', 3, 35.00, 'Vins et alcools Chevalier'),
(10273, 'TOMSP', 6, '2024-10-02', '2024-10-30', '2024-10-10', 1, 18.00, 'Toms Spezialitäten'),
(10274, 'HANAR', 4, '2024-10-03', '2024-11-01', '2024-10-12', 2, 60.00, 'Hanari Carnes'),
(10275, 'VICTE', 3, '2024-10-04', '2024-11-02', '2024-10-15', 1, 40.00, 'Victuailles en stock'),
(10276, 'SUPRD', 4, '2024-10-05', '2024-11-03', '2024-10-11', 2, 50.00, 'Suprêmes délices'),
(10277, 'HANAR', 3, '2024-10-06', '2024-10-20', '2024-10-16', 2, 45.00, 'Hanari Carnes'),
(10278, 'CHOPS', 5, '2024-10-07', '2024-11-05', '2024-10-23', 2, 20.00, 'Chop-suey Chinese'),
(10279, 'RICSU', 9, '2024-10-08', '2024-11-06', '2024-10-15', 3, 130.00, 'Richter Supermarkt'),
(10280, 'VINET', 5, '2024-11-01', '2024-11-29', '2024-11-15', 3, 30.00, 'Vins et alcools Chevalier'),
(10281, 'TOMSP', 6, '2024-11-02', '2024-11-30', '2024-11-10', 1, 15.00, 'Toms Spezialitäten'),
(10282, 'HANAR', 4, '2024-11-03', '2024-12-01', '2024-11-12', 2, 55.00, 'Hanari Carnes'),
(10283, 'VICTE', 3, '2024-11-04', '2024-12-02', '2024-11-15', 1, 35.00, 'Victuailles en stock'),
(10284, 'SUPRD', 4, '2024-11-05', '2024-12-03', '2024-11-11', 2, 45.00, 'Suprêmes délices'),
(10285, 'HANAR', 3, '2024-11-06', '2024-11-20', '2024-11-16', 2, 40.00, 'Hanari Carnes'),
(10286, 'CHOPS', 5, '2024-11-07', '2024-12-05', '2024-11-23', 2, 18.00, 'Chop-suey Chinese'),
(10287, 'RICSU', 9, '2024-11-08', '2024-12-06', '2024-11-15', 3, 120.00, 'Richter Supermarkt'),
(10288, 'VINET', 5, '2024-12-01', '2024-12-29', '2024-12-15', 3, 50.00, 'Vins et alcools Chevalier'),
(10289, 'TOMSP', 6, '2024-12-02', '2024-12-30', '2024-12-10', 1, 25.00, 'Toms Spezialitäten'),
(10290, 'HANAR', 4, '2024-12-03', '2025-01-01', '2024-12-12', 2, 75.00, 'Hanari Carnes'),
(10291, 'VICTE', 3, '2024-12-04', '2025-01-02', '2024-12-15', 1, 55.00, 'Victuailles en stock'),
(10292, 'SUPRD', 4, '2024-12-05', '2025-01-03', '2024-12-11', 2, 65.00, 'Suprêmes délices'),
(10293, 'HANAR', 3, '2024-12-06', '2024-12-20', '2024-12-16', 2, 60.00, 'Hanari Carnes'),
(10294, 'CHOPS', 5, '2024-12-07', '2025-01-05', '2024-12-23', 2, 30.00, 'Chop-suey Chinese'),
(10295, 'RICSU', 9, '2024-12-08', '2025-01-06', '2024-12-15', 3, 160.00, 'Richter Supermarkt'),
(10296, 'VINET', 5, '2024-12-09', '2025-01-07', '2024-12-16', 3, 45.00, 'Vins et alcools Chevalier'),
(10297, 'TOMSP', 6, '2024-12-10', '2025-01-08', '2024-12-17', 1, 20.00, 'Toms Spezialitäten'),
(10298, 'HANAR', 4, '2024-12-11', '2025-01-09', '2024-12-18', 2, 70.00, 'Hanari Carnes'),
(10299, 'VICTE', 3, '2024-12-12', '2025-01-10', '2024-12-19', 1, 50.00, 'Victuailles en stock'),
(10300, 'SUPRD', 4, '2024-12-13', '2025-01-11', '2024-12-20', 2, 60.00, 'Suprêmes délices'),
(10301, 'HANAR', 3, '2024-12-14', '2024-12-28', '2024-12-21', 2, 55.00, 'Hanari Carnes'),
(10302, 'CHOPS', 5, '2024-12-15', '2025-01-13', '2024-12-22', 2, 25.00, 'Chop-suey Chinese'),
(10303, 'CHOPS', 9, '2024-12-16', '2025-01-14', '2024-12-23', 3, 150.00, 'Richter Supermarkt');

INSERT INTO OrderDetails VALUES
(10248, 11, 14.00, 12, 0),
(10249, 14, 18.60, 9, 0),
(10250, 41, 7.70, 10, 0),
(10250, 51, 42.40, 35, 0.15),
(10251, 22, 16.80, 6, 0.05),
(10252, 11, 14.00, 10, 0),
(10253, 77, 14.50, 5, 0),
(10254, 55, 10.00, 20, 0.08),
(10255, 99, 25.60, 15, 0.10),
(10256, 11, 14.00, 10, 0),
(10257, 14, 18.60, 9, 0),
(10258, 41, 7.70, 10, 0),
(10258, 51, 42.40, 35, 0.15),
(10259, 22, 16.80, 6, 0.05),
(10260, 11, 14.00, 10, 0),
(10260, 42, 9.80, 12, 0),
(10262, 55, 10.00, 20, 0.08),
(10263, 12, 30.80, 5, 0.15),
(10265, 14, 18.60, 9, 0),
(10265, 51, 42.40, 40, 0),
(10266, 41, 7.70, 10, 0),
(10267, 57, 15.60, 15, 0.05),
(10268, 11, 14.00, 10, 0),
(10268, 42, 9.80, 12, 0),
(10269, 77, 14.50, 5, 0),
(10270, 66, 21.40, 10, 0.07),
(10271, 99, 25.60, 15, 0.10),
(10271, 12, 30.80, 5, 0.15),
(10272, 42, 9.80, 12, 0),
(10273, 14, 18.60, 9, 0),
(10274, 51, 42.40, 35, 0.15),
(10275, 22, 16.80, 6, 0.05),
(10275, 57, 15.60, 15, 0.05),
(10276, 42, 9.80, 12, 0),
(10277, 77, 14.50, 5, 0),
(10277, 88, 19.80, 7, 0.05),
(10278, 55, 10.00, 20, 0.08),
(10279, 99, 25.60, 15, 0.10),
(10279, 12, 30.80, 5, 0.15),
(10280, 42, 9.80, 12, 0),
(10281, 14, 18.60, 9, 0),
(10281, 51, 42.40, 40, 0),
(10282, 41, 7.70, 10, 0),
(10283, 22, 16.80, 6, 0.05),
(10283, 57, 15.60, 15, 0.05),
(10284, 11, 14.00, 10, 0),
(10285, 88, 19.80, 7, 0.05),
(10286, 55, 10.00, 20, 0.08),
(10287, 99, 25.60, 15, 0.10),
(10287, 12, 30.80, 5, 0.15),
(10288, 42, 9.80, 12, 0),
(10288, 12, 8.00, 10, 0),
(10288, 14, 7.70, 7, 0),
(10289, 14, 18.60, 9, 0),
(10290, 41, 7.70, 10, 0),
(10290, 51, 42.40, 35, 0.15),
(10291, 22, 16.80, 6, 0.05),
(10291, 57, 15.60, 15, 0.05),
(10292, 11, 14.00, 10, 0),
(10292, 42, 9.80, 12, 0),
(10293, 77, 14.50, 5, 0),
(10293, 88, 19.80, 7, 0.05),
(10294, 55, 10.00, 20, 0.08),
(10294, 66, 21.40, 10, 0.07),
(10295, 99, 25.60, 15, 0.10),
(10295, 12, 30.80, 5, 0.15),
(10296, 11, 14.00, 10, 0),
(10296, 42, 9.80, 12, 0),
(10297, 14, 18.60, 9, 0),
(10297, 51, 42.40, 40, 0),
(10298, 41, 7.70, 10, 0),
(10299, 22, 16.80, 6, 0.05),
(10299, 57, 15.60, 15, 0.05),
(10300, 11, 14.00, 10, 0),
(10300, 42, 9.80, 12, 0),
(10301, 77, 14.50, 5, 0),
(10302, 55, 10.00, 20, 0.08),
(10302, 66, 21.40, 10, 0.07),
(10303, 99, 25.60, 15, 0.10),
(10303, 12, 30.80, 5, 0.15);

-- Solution
WITH orders_data AS
(
SELECT
    o.CustomerID,
    COUNT(od.OrderID) AS Total_orders,
    SUM(CASE WHEN MONTH(o.OrderDate) = (SELECT MAX(MONTH(OrderDate)) FROM Orders) THEN 1 ELSE 0 END) AS Last_Month_Orders
FROM Orders o
JOIN OrderDetails od
ON od.OrderID = o.OrderID
GROUP BY o.CustomerID
)
    
SELECT
    CustomerID,
    Total_orders,
    Last_Month_Orders
FROM orders_data
WHERE Last_Month_Orders >= Total_orders * 0.5;
Fork
Embed on website
