Скачать или смотреть Myntra Interview Questions

Sales Analysis for a certain campaign

Campaign : ABC, PQR, XYZ
Campagin_id | campaign_start | campaign_end


Sales_Data
Order_id | order_date | campagin_id | customer_id


Create Commands:
-- Create the Campaign table
CREATE TABLE Campaign (
Campaign_id SERIAL PRIMARY KEY,
Campaign_name VARCHAR(50),
Campaign_start DATE,
Campaign_end DATE
);

-- Insert data into the Campaign table
INSERT INTO Campaign (Campaign_name, Campaign_start, Campaign_end) VALUES
('ABC', '2024-01-01', '2024-01-10'),
('PQR', '2024-01-15', '2024-01-25'),
('XYZ', '2024-02-01', '2024-02-10');

-- Create the Sales_Data table
CREATE TABLE Sales_Data (
Order_id SERIAL PRIMARY KEY,
Order_date DATE,
Campaign_id INTEGER,
Customer_id INTEGER,
FOREIGN KEY (Campaign_id) REFERENCES Campaign(Campaign_id)
);

-- Insert data into the Sales_Data table for customers 101, 102, and 103 within their respective campaign dates
INSERT INTO Sales_Data (Order_date, Campaign_id, Customer_id) VALUES
('2024-01-02', 1, 101), -- within ABC campaign
('2024-01-03', 1, 101), -- within ABC campaign
('2024-01-04', 1, 101), -- within ABC campaign
('2024-01-11', 1, 101), -- within ABC campaign
('2024-02-04', 1, 101), -- within ABC campaign
('2024-02-15', 3, 101), -- within XYZ campaign


('2024-01-03', 1, 102), -- within ABC campaign
('2024-01-03', 1, 102), -- within ABC campaign


('2024-01-16', 2, 102), -- within PQR campaign
('2024-01-17', 2, 102), -- within PQR campaign
('2024-01-26', 2, 102), -- within PQR campaign

('2024-02-15', 3, 103), -- within XYZ campaign
('2024-02-03', 3, 103), -- within XYZ campaign
('2024-02-11', 3, 103); -- within XYZ campaign




– For each customer_id get the number of times they have shopped within the campaign

– Customer_id c1 | c2 | c3