Write a query to retrieve the first order placed by each customer.

This question was asked in Interview at black rock. Read more

Company: BlackRock

CTC: 26LPA

Source: LinkedIn

SQL Interview Question

Q. Write a query to retrieve the first order placed by each customer.

-- Create the Customers table
CREATE TABLE Customers (
    CustomerID INT PRIMARY KEY,
    FirstName VARCHAR(50),
    LastName VARCHAR(50),
    Email VARCHAR(100),
    City VARCHAR(50)
);

-- Create the Orders table
CREATE TABLE Orders (
    OrderID INT PRIMARY KEY,
    CustomerID INT,
    OrderDate DATE,
    TotalAmount DECIMAL(10, 2),
    FOREIGN KEY (CustomerID) REFERENCES Customers(CustomerID)
);

-- Insert sample data into Customers table
INSERT INTO Customers VALUES
(1, 'John', 'Doe', 'john.doe@example.com', 'New York'),
(2, 'Jane', 'Smith', 'jane.smith@example.com', 'Los Angeles'),
(3, 'Emily', 'Johnson', 'emily.j@example.com', 'Chicago'),
(4, 'Michael', 'Brown', 'michael.b@example.com', 'Houston');

see this code on db-fiddle

Solution

Solution 1

SELECT 
 customerid, 
    MIN(OrderDate) AS first_order_date
FROM Orders
GROUP BY customerid;

Solution 2

SELECT *
    FROM (
        SELECT
                *,
               RANK() OVER (PARTITION BY CustomerID ORDER BY OrderDate ASC) AS order_num
        FROM Orders
     ) AS RankedOrders
WHERE order_num = 1;

Solution 3

WITH RankedOrders AS (
    SELECT 
        *,
        RANK() OVER (PARTITION BY CustomerID ORDER BY OrderDate ASC) AS order_num
    FROM Orders
)
SELECT *
FROM RankedOrders
WHERE order_num = 1;

Explanation

While there are multiple ways to solve this problem, but most simple answer would be the solution 1. Wherein we have just took minimum on order date.

I hope this would have been helpful for you, consider sharing it with your friends. thank you.

Vishal

Hi, I am Vishal Jaiswal, I have about a decade of experience of working in MNCs like Genpact, Savista, Ingenious. Currently i am working in EXL as a senior quality analyst. Using my writing skills i want to share the experience i have gained and help as many as i can.

Spread the love