lab work 5.2.txt

-- Table 1: Orders (Q1.1, Q2.3)
CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    customer_id INT,
    order_date DATE
);

-- Table 2: Sales (Q1.2, Q1.5, Q2.4, Q2.5)
CREATE TABLE sales (
    sale_id INT PRIMARY KEY,
    order_id INT,
    product_id INT,
    category VARCHAR(50),
    region VARCHAR(50),
    sales DECIMAL(10, 2),
    order_date DATE
);

-- Table 3: Employees (Q1.3, Q2.2)
CREATE TABLE employees (
    employee_id INT PRIMARY KEY,
    department VARCHAR(50),
    salary DECIMAL(10, 2)
);

-- Table 4: Customers (Q1.4)
CREATE TABLE customers (
    customer_id INT PRIMARY KEY,
    customer_name VARCHAR(50),
    total_purchase DECIMAL(10, 2)
);

-- Table 5: Products (Q2.1)
CREATE TABLE products (
    product_id INT PRIMARY KEY,
    product_name VARCHAR(50),
    category VARCHAR(50),
    sales DECIMAL(10, 2) -- Yahan sales product-level par hai for top 3
);


-- Insert into Orders
INSERT INTO orders VALUES (1, 101, '2024-01-01'), (2, 102, '2024-01-05'), (3, 101, '2024-01-10');

-- Insert into Sales (Sample for last 7 days calculation)
INSERT INTO sales VALUES 
(1, 1, 10, 'Electronics', 'North', 500.00, '2024-01-01'),
(2, 2, 11, 'Furniture', 'South', 300.00, '2024-01-02'),
(3, 3, 10, 'Electronics', 'North', 700.00, '2024-01-03'),
(4, 4, 12, 'Electronics', 'West', 1000.00, '2024-01-04'),
(5, 5, 10, 'Electronics', 'North', 150.00, '2024-01-05'),
(6, 6, 11, 'Furniture', 'South', 400.00, '2024-01-06'),
(7, 7, 10, 'Electronics', 'North', 200.00, '2024-01-07'),
(8, 8, 10, 'Electronics', 'North', 300.00, '2024-01-08');

-- Insert into Employees
INSERT INTO employees VALUES 
(1, 'HR', 50000), (2, 'HR', 60000), 
(3, 'IT', 80000), (4, 'IT', 90000), (5, 'IT', 70000);

-- Insert into Customers
INSERT INTO customers VALUES 
(101, 'Rahul', 5000), (102, 'Priya', 8000), (103, 'Amit', 5000), (104, 'Sonia', 2000);

-- Insert into Products (for Top 3 per category)
INSERT INTO products VALUES 
(1, 'Laptop', 'Electronics', 50000), (2, 'Phone', 'Electronics', 40000), 
(3, 'Tablet', 'Electronics', 30000), (4, 'Mouse', 'Electronics', 1000),
(5, 'Chair', 'Furniture', 5000), (6, 'Table', 'Furniture', 10000);

QUESTION :- 1

-- 1. Order date ke basis par row number (latest first)
SELECT order_id, order_date, ROW_NUMBER() OVER (ORDER BY order_date DESC) AS row_num FROM orders;

-- 2. Product category ke liye running total of sales
SELECT category, order_date, sales, SUM(sales) OVER (PARTITION BY category ORDER BY order_date) AS running_total FROM sales;

-- 3. Department-wise average salary (employee salary visible rahe)
SELECT employee_id, department, salary, AVG(salary) OVER (PARTITION BY department) AS avg_salary FROM employees;

-- 4. Customers ko total purchase ke basis par rank (ties same rank)
SELECT customer_id, total_purchase, RANK() OVER (ORDER BY total_purchase DESC) AS customer_rank FROM customers;

-- 5. Order sales aur category average sales ka difference
SELECT order_id, category, sales, sales - AVG(sales) OVER (PARTITION BY category) AS sales_difference FROM sales;

QUESTION :- 2

-- 1. Har category ke top 3 highest-selling products
SELECT * FROM ( SELECT product_id, category, sales, RANK() OVER (PARTITION BY category ORDER BY sales DESC) AS product_rank FROM products ) ranked_products WHERE product_rank <= 3;

-- 2. Department ke total salary me employee ka percentage contribution
SELECT employee_id, department, salary, (salary * 100.0 / SUM(salary) OVER (PARTITION BY department)) AS salary_percentage FROM employees;

-- 3. Har customer ke previous aur next order date
SELECT customer_id, order_date, LAG(order_date) OVER (PARTITION BY customer_id ORDER BY order_date) AS previous_order, LEAD(order_date) OVER (PARTITION BY customer_id ORDER BY order_date) AS next_order FROM orders;

-- 4. Last 7 days ka moving average of sales (per product)
SELECT product_id, order_date, sales, AVG(sales) OVER (PARTITION BY product_id ORDER BY order_date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS moving_avg_7_days FROM sales;

-- 5. Region-wise highest aur lowest sales (records visible rahen)
SELECT region, sales, MAX(sales) OVER (PARTITION BY region) AS highest_sales, MIN(sales) OVER (PARTITION BY region) AS lowest_sales FROM sales;