Advanced SQL - Find Out Which Supplier Had The Highest Sales Using Common Table Expression

SQL QUESTION: Find out which suppliers had the highest average sales in January 2023.
 This is Episode 7 of 30 Days Advanced SQL Series
We will be Using these tables: three main tables (sales, products and suppliers
 
I have already provided the insert tables and insert records script below that you can use and practice with me. Let's Dive In.
------------------------------------------------------------------------------------------------------------
CREATE TABLE products (
product_id INT PRIMARY KEY,
product_name VARCHAR(255) NOT NULL,
supplier_id INT
);
-------------------------------------------------------------------------------------------------------------
-- Sample Products
INSERT INTO products (product_id, product_name, supplier_id)
VALUES
(1, 'Laptop', 1),
(2, 'Mobile', 2),
(3, 'Headphones', 3);
-------------------------------------------------------------------------------------------------------------
CREATE TABLE suppliers (
supplier_id INT PRIMARY KEY,
supplier_name VARCHAR(255) NOT NULL
);
------------------------------------------------------------------------------------------------------------
-- Sample Suppliers
INSERT INTO suppliers (supplier_id, supplier_name)
VALUES
(1, 'TechCorp'),
(2, 'PhoneWorld'),
(3, 'SoundScape');
------------------------------------------------------------------------------------------------------------
CREATE TABLE suppliers (
supplier_id INT PRIMARY KEY,
supplier_name VARCHAR(255) NOT NULL
);
------------------------------------------------------------------------------------------------------------
-- Sample Suppliers
INSERT INTO suppliers (supplier_id, supplier_name)
VALUES
(1, 'TechCorp'),
(2, 'PhoneWorld'),
(3, 'SoundScape');
------------------------------------------------------------------------------------------------------------
CREATE TABLE sales (
sale_id INT PRIMARY KEY,
product_id INT,
amount DECIMAL(10,2), -- The sales value for each transaction
transaction_date DATE,
FOREIGN KEY (product_id) REFERENCES products(product_id)
);
------------------------------------------------------------------------------------------------------------
-- Sample Sales Records
-- Assuming Laptop costs $1000, Mobile costs $500, and Headphones cost $150
INSERT INTO sales (sale_id, product_id, amount, transaction_date)
VALUES
(1, 1, 1000, '2023-01-10'),
(2, 2, 500, '2023-01-15'),
(3, 3, 150, '2023-01-20'),
(4, 1, 1000, '2023-01-25'),
(5, 1, 1000, '2023-01-30');
------------------------------------------------------------------------------------------------------------
#Sqlcoding #windowfunction #CTE #commontableexpression #WITHCTE #Having #DateBetween #sqlServer #businessAnalyst #FAANGSQL #AppleInterview #AppleInterviewQuestion #SQLJobs

Видео Advanced SQL - Find Out Which Supplier Had The Highest Sales Using Common Table Expression канала Sidra Codes