FREE AI-Enhanced Online SQL Server Compiler

Untitled*

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

CREATE TABLE customers (
    customer_id INT PRIMARY KEY,
    customer_name VARCHAR(50),
    city VARCHAR(50)
);
CREATE TABLE orders (
    order_id INT PRIMARY    KEY,
    customer_id INT,
    order_date DATE,
    total_amount DECIMAL(10,2),
    FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);
CREATE    TABLE products (
    product_id INT PRIMARY KEY,
    product_name VARCHAR(50),   
    category VARCHAR(50)
);
CREATE TABLE order_details (
    order_id INT,
    product_id INT,
    quantity INT,
    PRIMARY KEY (order_id, product_id),
    FOREIGN KEY (order_id) REFERENCES orders(order_id),   
    FOREIGN KEY (product_id) REFERENCES products(product_id)   
);

INSERT INTO customers (customer_id, customer_name, city) VALUES
(1, 'Alice Brown', 'Los Angeles'),
(2, 'John D', 'Los Angeles'),
(3, 'Charlie M', 'Los Angeles'),
(4, 'Julie Prince', 'Los Angeles'),
(5, 'Ethan Hunt', 'San Francisco'),
(6, 'Mark Antony', 'Los Angeles'),
(7, 'Tim Criag', 'Los Angeles'),
(8, 'Emily C', 'San Francisco');

INSERT INTO orders (order_id, customer_id, order_date, total_amount) VALUES
(101, 1, '2024-01-15', 250.75),
(102, 2, '2024-01-16', 150.00),
(103, 4, '2024-01-17', 300.50),
(104, 3, '2024-01-18', 450.25),
(105, 5, '2024-01-19', 125.00),
(106, 6, '2024-02-17', 100.50),
(107, 7, '2024-03-18', 250.25),
(108, 8, '2024-04-19', 725.00);

INSERT INTO products (product_id, product_name, category) VALUES
(201, 'Laptop', 'Electronics'),
(202, 'Smartphone', 'Electronics'),
(203, 'Coffee Maker', 'Home Appliances'),
(204, 'Headphones', 'Electronics'),
(205, 'Blender', 'Home Appliances');

INSERT INTO order_details (order_id, product_id, quantity) VALUES
(101, 201, 1),
(101, 202, 2),
(102, 203, 1),
(103, 204, 3),
(104, 201, 1),
(105, 205, 2);

WITH CustomerOrderValue AS (
  SELECT c.city, c.customer_name, AVG(o.total_amount) AS avg_order_value
  FROM customers c
  INNER JOIN orders o ON c.customer_id = o.customer_id
  WHERE o.order_date >= DATEADD(year, -1, CURRENT_TIMESTAMP)
  GROUP BY c.city, c.customer_name
),
RankedCustomers AS (
  SELECT city, customer_name, avg_order_value,
         DENSE_RANK() OVER (PARTITION BY city ORDER BY avg_order_value DESC) AS rank
  FROM CustomerOrderValue
)
SELECT city, customer_name, avg_order_value, Rank
FROM RankedCustomers
WHERE rank <= 3
;

city customer_name avg_order_value Rank
-------------------------------------------------- -------------------------------------------------- ---------------------------------------- --------------------
Los Angeles Charlie M 450.250000 1
Los Angeles Julie Prince 300.500000 2
Los Angeles Alice Brown 250.750000 3
San Francisco Emily C 725.000000 1
San Francisco Ethan Hunt 125.000000 2