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;

-- 1. Average order value and number of orders per customer in the last 30 days, by loyalty tier
WITH recent_orders AS (
    SELECT c.loyalty_tier, c.customer_id, o.total_amount
    FROM customers c
    JOIN orders o ON c.customer_id = o.customer_id
    WHERE o.order_date >= DATEADD(DAY, -30, '2025-01-12')
)
SELECT 
    loyalty_tier,
    AVG(total_amount) AS avg_order_value,
    COUNT(*) / COUNT(DISTINCT customer_id) AS avg_orders_per_customer
FROM recent_orders
GROUP BY loyalty_tier;

loyalty_tier	avg_order_value	avg_orders_per_customer
Bronze	418.714285	1
Gold	514.375000	1
Platinum	397.333333	1
Silver	318.142857	1

(100 rows affected)

(100 rows affected)

(100 rows affected)

(100 rows affected)

(20 rows affected)

(4 rows affected)