-- Create a database. Replace your_name with your own name prefix.
CREATE DATABASE IF NOT EXISTS DataLakeCatalog.your_name_database;

-- Create a table.
CREATE TABLE IF NOT EXISTS DataLakeCatalog.your_name_database.sales_data (
    date     STRING,
    product  STRING,
    region   STRING,
    quantity INT,
    revenue  DOUBLE
);

-- Write 15 rows of sample data.
INSERT INTO DataLakeCatalog.your_name_database.sales_data VALUES
('2025-01-01', 'Widget A', 'North', 239, 6915.68),
('2025-01-01', 'Widget B', 'South', 185, 12430.50),
('2025-01-01', 'Widget C', 'East',  327, 28025.76),
('2025-01-01', 'Widget D', 'North', 340, 8986.75),
('2025-01-01', 'Widget E', 'East',  421, 25079.72),
('2025-01-02', 'Widget A', 'West',  150, 4350.00),
('2025-01-02', 'Widget B', 'North', 275, 18562.50),
('2025-01-02', 'Widget C', 'South', 310, 26556.00),
('2025-01-02', 'Widget D', 'East',  198, 5237.10),
('2025-01-02', 'Widget E', 'West',  460, 27416.00),
('2025-01-03', 'Widget A', 'East',  305, 8845.00),
('2025-01-03', 'Widget B', 'West',  220, 14850.00),
('2025-01-03', 'Widget C', 'North', 290, 24824.00),
('2025-01-03', 'Widget D', 'South', 175, 4627.50),
('2025-01-03', 'Widget E', 'North', 388, 23124.80);

-- Verification: 15 rows should be returned.
SELECT COUNT(*) AS total_rows FROM DataLakeCatalog.your_name_database.sales_data;

DESCRIBE DataLakeCatalog.your_name_database.sales_data;

SELECT * FROM DataLakeCatalog.your_name_database.sales_data LIMIT 10;

-- Regional sales overview (used as the dataset for dashboard KPIs and bar charts)
SELECT
    region,
    SUM(quantity)               AS total_quantity,
    SUM(revenue)                AS total_revenue,
    ROUND(AVG(revenue / quantity), 2) AS avg_unit_price
FROM DataLakeCatalog.your_name_database.sales_data
GROUP BY region
ORDER BY total_revenue DESC;

-- Daily sales trend (used as the dataset for dashboard line charts)
SELECT
    date,
    SUM(revenue)  AS daily_revenue,
    SUM(quantity) AS daily_quantity
FROM DataLakeCatalog.your_name_database.sales_data
GROUP BY date
ORDER BY date;

-- Product sales ranking by region (used as the dataset for the dashboard table component)
SELECT
    region,
    product,
    SUM(revenue) AS product_revenue,
    RANK() OVER (PARTITION BY region ORDER BY SUM(revenue) DESC) AS revenue_rank
FROM DataLakeCatalog.your_name_database.sales_data
GROUP BY region, product
ORDER BY region, revenue_rank;

-- Parameterization: filter by region and time window
SELECT
    region,
    product,
    SUM(revenue) AS total_revenue
FROM DataLakeCatalog.your_name_database.sales_data
WHERE region = '${target_region}'
  AND date BETWEEN '${start_date}' AND '${end_date}'
GROUP BY region, product
ORDER BY total_revenue DESC;