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;

--select * from orders;
-- 3. Percentage of customers in each loyalty tier who made a purchase in the last 60 days
WITH customer_purchase AS (
    SELECT 
        c.loyalty_tier,
        c.customer_id,
        MAX(CASE WHEN o.order_date >= DATEADD(DAY, -60, '2025-01-12') THEN 1 ELSE 0 END) AS has_recent_purchase
    FROM customers c
    LEFT JOIN orders o ON c.customer_id = o.customer_id
    GROUP BY c.loyalty_tier, c.customer_id
)
SELECT 
    loyalty_tier,
    AVG(cast(has_recent_purchase as FLOAT)) * 100 AS percentage_with_recent_purchase
FROM customer_purchase
GROUP BY loyalty_tier;

loyalty_tier	percentage_with_recent_purchase
Bronze	36.0
Gold	32.0
Platinum	32.0
Silver	28.000000000000004

(100 rows affected)

(100 rows affected)

(100 rows affected)

(100 rows affected)

(20 rows affected)

(4 rows affected)