This question was asked in Interview at black rock. Read more
Company: BlackRock
CTC: 26LPA
Source: LinkedIn
-- 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
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;
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.
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.
Python Practice Questions & Solutions Day 5 of Learning Python for Data Science Welcome back…
Day 5 of Learning Python for Data Science: Data Types, Typecasting, Indexing, and Slicing Understanding…
Python Practice Questions & Solutions Day 4 of Learning Python for Data Science Welcome back…
Day 4 of Learning Python for Data Science Day 4 of Learning Python for Data…
Test your Python skills with these 20 practice questions and solutions from Day 3 of…
Understanding Python’s conditional statements is essential for controlling the flow of a program. Today, we…