-- Create fact table with CTAS
CREATE TABLE [dbo].[FactSales]
WITH
(
    DISTRIBUTION = HASH(date_key),
    CLUSTERED COLUMNSTORE INDEX,
    PARTITION = (date_key RANGE RIGHT FOR VALUES
        (20240101, 20240201, 20240301, 20240401,
         20240501, 20240601, 20240701, 20240801,
         20240901, 20241001, 20241101, 20241201))
)
AS
SELECT
    s.sale_id,
    d.date_key,
    c.customer_key,
    p.product_key,
    s.quantity,
    s.unit_price,
    s.quantity * s.unit_price AS total_amount,
    s.region,
    GETDATE() AS load_date
FROM [staging].[Sales] s
INNER JOIN [dbo].[DimDates] d
    ON s.sale_date = d.full_date
INNER JOIN [dbo].[DimCustomers] c
    ON s.customer_id = c.customer_id
INNER JOIN [dbo].[DimProducts] p
    ON s.product_id = p.product_id;

-- Create dimension table with replicated distribution
CREATE TABLE [dbo].[DimCustomers]
WITH
(
    DISTRIBUTION = REPLICATE,
    CLUSTERED INDEX (customer_key)
)
AS
SELECT
    customer_id AS customer_key,
    customer_name,
    email,
    segment,
    region,
    GETDATE() AS load_date
FROM [staging].[Customers];

-- Create aggregate table
CREATE TABLE [dbo].[AggDailySalesByRegion]
WITH
(
    DISTRIBUTION = HASH(region),
    CLUSTERED COLUMNSTORE INDEX
)
AS
SELECT
    date_key,
    region,
    SUM(total_amount) AS daily_revenue,
    COUNT(*) AS transaction_count,
    COUNT(DISTINCT customer_key) AS unique_customers
FROM [dbo].[FactSales]
GROUP BY date_key, region;