FREE AI-Enhanced Online SQL Server Compiler

Untitled*

You must execute the code again to save those values in the database.

CREATE TABLE stores (
    store_id INT PRIMARY KEY,
    store_name VARCHAR(50),
    city VARCHAR(50)
);

CREATE    TABLE products (
    product_id INT PRIMARY KEY,
    product_name VARCHAR(50),   
    category VARCHAR(50)
);

CREATE TABLE sales (
    sale_id INT PRIMARY KEY,
    product_id INT,
    store_id INT,
    sale_date DATE,
    quantity INT,
    FOREIGN KEY (product_id) REFERENCES products(product_id),
    FOREIGN KEY (store_id) REFERENCES stores(store_id)
);

INSERT INTO stores (store_id, store_name, city) VALUES
(1, 'Tech Haven', 'New York'),
(2, 'Gadget Galaxy', 'Los Angeles'),
(3, 'Home Essentials', 'Chicago'),
(4, 'ElectroMart', 'Miami'),
(5, 'Smart Shop', 'San Francisco');

INSERT INTO products (product_id, product_name, category) VALUES
(201, 'Laptop', 'Electronics'),
(202, 'Smartphone', 'Electronics'),
(203, 'Coffee Maker', 'Home Appliances'),
(204, 'Diapers', 'Baby Products'),
(205, 'Cribs', 'Baby Products'),
(206, 'Television', 'Electronics'),
(207, 'Headphones', 'Electronics'),
(208, 'Microwave Oven', 'Home Appliances'),
(209, 'Refrigerator', 'Home Appliances'),
(210, 'Washing Machine', 'Home Appliances'),
(211, 'Air Conditioner', 'Home Appliances'),
(212, 'Smartwatch', 'Electronics'),
(213, 'Gaming Console', 'Electronics'),
(214, 'Coffee Table', 'Furniture'),
(215, 'Sofa', 'Furniture'),
(216, 'Dining Table', 'Furniture'),
(217, 'Office Chair', 'Furniture'),
(218, 'Backpack', 'Fashion'),
(219, 'Sneakers', 'Fashion'),
(220, 'T-shirt', 'Fashion'),
(221, 'Jeans', 'Fashion'),
(222, 'Water Bottle', 'Sports & Outdoors'),
(223, 'Yoga Mat', 'Sports & Outdoors'),
(224, 'Bicycle', 'Sports & Outdoors'),
(225, 'Camping Tent', 'Sports & Outdoors');

INSERT INTO sales (sale_id, product_id, store_id, sale_date, quantity) VALUES
(1001, 201, 1, '2023-01-10', 5),
(1002, 202, 2, '2023-01-12', 1),
(1003, 203, 3, '2024-01-15', 3),
(1004, 204, 4, '2023-01-20', 8),
(1008, 204, 1, '2024-01-20', 1),
(1005, 205, 5, '2024-01-25', 4),
(1006, 201, 1, '2024-02-01', 7),
(1007, 202, 2, '2024-02-05', 1), 
(1011, 214, 2, '2023-05-01', 5),
(1012, 214, 1, '2024-03-01', 9),
(1013, 225, 2, '2023-01-01', 5),
(1014, 223, 3, '2024-03-01', 6),
(1015, 218, 1, '2023-01-01', 5),
(1016, 219, 3, '2024-03-01', 6);

/*WITH ProductCategorySales AS (
  SELECT p.category, YEAR(s.sale_date) AS year, SUM(s.quantity) AS total_sales
  FROM products p
  INNER JOIN sales s ON p.product_id = s.product_id
  GROUP BY p.category, YEAR(s.sale_date)
),
GrowthRates AS (
  SELECT category,
         (SUM(CASE WHEN year = YEAR(GETDATE()) THEN total_sales ELSE 0 END) -
          SUM(CASE WHEN year = YEAR(GETDATE()) - 1 THEN total_sales ELSE 0 END)) * 100.0 /
          NULLIF(SUM(CASE WHEN year = YEAR(GETDATE()) - 1 THEN total_sales ELSE 0 END), 0) AS growth_rate
  FROM ProductCategorySales
  GROUP BY category
)
SELECT TOP 3 category,growth_rate
FROM GrowthRates
WHERE growth_rate > 0  -- Include only positive growth rates
ORDER BY growth_rate DESC;*/

WITH ProductCategorySales AS (
  SELECT p.category, YEAR(s.sale_date) AS year, SUM(s.quantity) AS total_sales
  FROM products p
  INNER JOIN sales s ON p.product_id = s.product_id
  GROUP BY p.category, YEAR(s.sale_date)
),
GrowthRates AS (
  SELECT category,
         (SUM(CASE WHEN year = YEAR(GETDATE()) THEN total_sales ELSE 0 END) -
          SUM(CASE WHEN year = YEAR(GETDATE()) - 1 THEN total_sales ELSE 0 END)) * 100.0 /
          NULLIF(SUM(CASE WHEN year = YEAR(GETDATE()) - 1 THEN total_sales ELSE 0 END), 0) AS growth_rate
  FROM ProductCategorySales
  GROUP BY category
),
RankedGrowthRates AS (
  SELECT category, growth_rate,
         DENSE_RANK() OVER (ORDER BY growth_rate DESC) AS rank
  FROM GrowthRates
)
SELECT category, growth_rate
FROM RankedGrowthRates
WHERE rank <= 3  -- Include all categories with a rank of 3 or better
  AND growth_rate > 0  -- Include only positive growth rates
ORDER BY growth_rate DESC;

category	growth_rate
Furniture	80.000000000000
Electronics	33.333333333333
Fashion	20.000000000000
Sports & Outdoors	20.000000000000

(5 rows affected)

(25 rows affected)

(14 rows affected)

(4 rows affected)