Advanced SQL - Analyzing Product Performance and Cumulative Sales (Running Total)
HTML-код
- Опубликовано: 16 окт 2024
- Learn Advanced SQL - Analyzing Product Performance and Cumulative Sales (Running Total) . This is Episode 8 of 30 day Advanced SQL series.
Below is the tables and records script for this video. So you can create and learn as we go:
-------------------------------------------------------------------------------------------------------------------------------------------------
Creating the 'products' table
CREATE TABLE products (
product_id INT PRIMARY KEY,
product_name VARCHAR(255),
supplier_id INT -- Assuming this is a foreign key linking to the 'suppliers' table
);
---------------------------------------------------------------------------------------------------------------------------------------------------
INSERT INTO products (product_id, product_name, supplier_id)
VALUES
(1, 'Laptop', 1),
(2, 'Mobile', 2),
(3, 'Headphones', 3),
(4, 'Tablet', 1),
(5, 'SmartWatch', 2);
---------------------------------------------------------------------------------------------------------------------------------------------------
CREATE TABLE sales (
sale_id INT PRIMARY KEY,
product_id INT, -- Foreign key linking to 'products'
amount DECIMAL(10, 2),
transaction_date DATE
);
---------------------------------------------------------------------------------------------------------------------------------------------------
INSERT INTO sales (sale_id, product_id, amount, transaction_date)
VALUES
(1, 1, 2000, '2023-01-01'),
(2, 2, 1500, '2023-01-01'),
(3, 3, 450, '2023-01-01'),
(4, 1, 3000, '2023-01-02'),
(5, 2, 1000, '2023-01-02'),
(6, 3, 550, '2023-01-02'),
(7, 1, 2500, '2023-01-03'),
(8, 2, 1300, '2023-01-03'),
(9, 3, 600, '2023-01-03'),
(10, 4, 400, '2023-01-01'),
(11, 5, 200, '2023-01-01'),
(12, 1, 900, '2023-01-02'),
(13, 2, 1100, '2023-01-02'),
(14, 4, 600, '2023-01-02'),
(15, 5, 250, '2023-01-02'),
(16, 1, 1000, '2023-01-03'),
(17, 2, 900, '2023-01-03'),
(18, 4, 700, '2023-01-03'),
(19, 5, 280, '2023-01-03');
---------------------------------------------------------------------------------------------------------------------------------------------------
CREATE TABLE suppliers (
supplier_id INT PRIMARY KEY,
supplier_name VARCHAR(255)
);
---------------------------------------------------------------------------------------------------------------------------------------------------
INSERT INTO suppliers (supplier_id, supplier_name)
VALUES
(1, 'TechCorp'),
(2, 'PhonePros'),
(3, 'AudioWorld');
---------------------------------------------------------------------------------------------------------------------------------------------------
#Sqlcoding #windowfunction #CTE #commontableexpression #WITHCTE #Having #DateBetween #sqlServer #businessAnalyst #FAANGSQL #AppleInterview #AppleInterviewQuestion #cumulativeSales
#SQLCUmulative #SQLRANk #DenseRank #SubQuery #QueryOptimization #SqlOptimization
#SQLTop5 #SQLHighest
'promosm'