create_data_set.sql

-- create datawarehouse
CREATE WAREHOUSE 
demo_wh WITH     
WAREHOUSE_SIZE = 'XSMALL'     
AUTO_SUSPEND = 60    
AUTO_RESUME = TRUE     
INITIALLY_SUSPENDED = TRUE;

-- Create a new database 
CREATE DATABASE demo_db;  

-- Switch to use this database 
USE DATABASE demo_db;  

-- Create a schema within the database 
CREATE SCHEMA sales_schema;  

-- Set this as the active schema 
USE SCHEMA sales_schema;

-- Create a table to hold sales data
CREATE OR REPLACE 
TABLE sales 
(order_id INT,order_date DATE,region STRING,product STRING,amount FLOAT );

-- Generate and insert 1 million random sales records
INSERT INTO sales
SELECT
    -- Unique Order ID
    ROW_NUMBER() OVER (ORDER BY SEQ8()) AS order_id,

    -- Random Order Date (Between 2023-01-01 and ~2 years)
    DATEADD(
        day,
        UNIFORM(1, 730, RANDOM()),
        '2023-01-01'::DATE
    ) AS order_date,

    -- Random Region
    CASE UNIFORM(1, 5, RANDOM())
        WHEN 1 THEN 'North'
        WHEN 2 THEN 'South'
        WHEN 3 THEN 'East'
        WHEN 4 THEN 'West'
        ELSE 'Central'
    END AS region,

    -- Random Product
    CASE UNIFORM(1, 4, RANDOM())
        WHEN 1 THEN 'Laptop'
        WHEN 2 THEN 'Phone'
        WHEN 3 THEN 'Tablet'
        ELSE 'Monitor'
    END AS product,

    -- Random Sales Amount
    UNIFORM(100, 5000, RANDOM()) AS amount

FROM TABLE(GENERATOR(ROWCOUNT => 1000000));

-- Check total row count 
SELECT COUNT(*) as total_rows FROM sales;


-- View sample records 
SELECT * FROM sales LIMIT 10;

-- Data distribution by region 
SELECT      
region,      
COUNT(*) as row_count,     
ROUND(COUNT(*) / 10000.0, 1) as percentage FROM sales  
GROUP BY region  
ORDER BY region;