FREE AI-Enhanced Online SQL Server Compiler

Untitled*

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

-- customers table
CREATE TABLE customers (
    customer_id INT PRIMARY KEY,
    name VARCHAR(100),
    email VARCHAR(100),
    join_date DATE,
    loyalty_tier VARCHAR(20)
);

-- Create a temporary table with random numbers
CREATE TABLE #RandomNumbers (
    id INT IDENTITY(1,1),
    rand_num FLOAT
);

-- Insert random numbers
INSERT INTO #RandomNumbers (rand_num)
SELECT RAND() FROM GENERATE_SERIES(1, 100);

INSERT INTO customers (customer_id, name, email, join_date, loyalty_tier)
SELECT 
    gs.value,
    'Customer ' + CAST(gs.value AS VARCHAR(10)),
    'customer' + CAST(gs.value AS VARCHAR(10)) + '@email.com',
    --DATEADD(DAY, -CAST(rn.rand_num * 365 * 2 AS INT), '2024-07-16'),
    DATEADD(DAY, -ABS(CHECKSUM(NEWID()) % (365 * 2)), '2024-07-16'
    ),
    CASE 
        WHEN value % 4 = 1 THEN 'Bronze'
        WHEN value % 4 = 2 THEN 'Silver'
        WHEN value % 4 = 3 THEN 'Gold'
        ELSE 'Platinum'
    END
FROM GENERATE_SERIES(1, 100) gs
JOIN #RandomNumbers rn ON
gs.value = rn.id ;

-- orders table
CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    customer_id INT,
    order_date DATE,
    total_amount DECIMAL(10, 2)
);

INSERT INTO orders (order_id, customer_id, order_date, total_amount)
SELECT 
    value,
    1 + (value - 1) % 100,
    --DATEADD(DAY, -CAST(RAND() * 180 AS INT), '2025-01-12'),
    DATEADD(DAY, -ABS(CHECKSUM(NEWID()) % 180 ), '2025-01-12'),
    CAST(ABS(CHECKSUM(NEWID()) % 1000 + 50) AS DECIMAL(10, 2))
FROM GENERATE_SERIES(1, 100);

CREATE TABLE order_items (
    order_id INT,
    product_id INT,
    quantity INT,
    price DECIMAL(10, 2)
);

INSERT INTO order_items (order_id, product_id, quantity, price)
SELECT 
    value,
    1 + (value - 1) % 20,
    1 + CAST(ABS(CHECKSUM(NEWID()) % 5) AS INT),
    CAST(ABS(CHECKSUM(NEWID()) % 100 + 10) AS DECIMAL(10, 2))
FROM GENERATE_SERIES(1, 100);

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

INSERT INTO products (product_id, product_name, category)
SELECT 
    value,
    'Product ' + CAST(value AS VARCHAR(10)),
    CASE 
        WHEN value % 5 = 1 THEN 'Electronics'
        WHEN value % 5 = 2 THEN 'Clothing'
        WHEN value % 5 = 3 THEN 'Home & Garden'
        WHEN value % 5 = 4 THEN 'Books'
        ELSE 'Toys'
    END
FROM GENERATE_SERIES(1, 20);

-- Set the current date for our analysis
DECLARE @current_date DATE = '2025-01-12';

--Select @current_date;

-- 2. Top 5 products by revenue for each loyalty tier in the last 90 days
WITH product_revenue AS (
    SELECT 
        c.loyalty_tier,
        p.product_id,
        p.product_name,
        SUM(oi.quantity * oi.price) AS revenue,
        ROW_NUMBER() OVER (PARTITION BY c.loyalty_tier ORDER BY SUM(oi.quantity * oi.price) DESC) AS rank
    FROM customers c
    JOIN orders o ON c.customer_id = o.customer_id
    JOIN order_items oi ON o.order_id = oi.order_id
    JOIN products p ON oi.product_id = p.product_id
    WHERE o.order_date >= DATEADD(DAY, -90, '2025-01-12')
    GROUP BY c.loyalty_tier, p.product_id, p.product_name
)
SELECT loyalty_tier, product_id, product_name, revenue
FROM product_revenue
WHERE rank <= 5
ORDER BY loyalty_tier, revenue DESC;

loyalty_tier	product_id	product_name	revenue
Bronze	5	Product 5	607.00
Bronze	17	Product 17	576.00
Bronze	13	Product 13	525.00
Bronze	9	Product 9	252.00
Bronze	1	Product 1	63.00
Gold	11	Product 11	836.00
Gold	3	Product 3	510.00
Gold	15	Product 15	485.00
Gold	19	Product 19	418.00
Gold	7	Product 7	3.00
Platinum	4	Product 4	473.00
Platinum	8	Product 8	355.00
Platinum	16	Product 16	291.00
Platinum	12	Product 12	219.00
Platinum	20	Product 20	108.00
Silver	10	Product 10	1855.00
Silver	6	Product 6	826.00
Silver	2	Product 2	520.00
Silver	18	Product 18	253.00
Silver	14	Product 14	68.00

(100 rows affected)

(100 rows affected)

(100 rows affected)

(100 rows affected)

(20 rows affected)

(20 rows affected)