E-Commerce Orders

-- demo_ecommerce.lua — E-Commerce order data (2,000 orders over 6 months)
--
-- Great for: bar charts (revenue by category), City View (height=total,
-- color=category, district=region), timeline scrubbing by order_date.

ds.log("=== E-Commerce Orders Demo ===")

ds.query([[
    WITH raw AS (
        SELECT
            date '2025-07-01' + INTERVAL (floor(random() * 180)::int) DAY  AS order_date,
            1000 + i                                                        AS order_id,

            -- 6 regions with weighted distribution
            CASE
                WHEN random() < 0.25 THEN 'North America'
                WHEN random() < 0.45 THEN 'Europe'
                WHEN random() < 0.60 THEN 'Asia Pacific'
                WHEN random() < 0.75 THEN 'Latin America'
                WHEN random() < 0.90 THEN 'Middle East'
                ELSE                      'Africa'
            END AS region,

            -- 8 product categories
            CASE
                WHEN random() < 0.18 THEN 'Electronics'
                WHEN random() < 0.32 THEN 'Clothing'
                WHEN random() < 0.44 THEN 'Home & Garden'
                WHEN random() < 0.55 THEN 'Sports'
                WHEN random() < 0.65 THEN 'Books'
                WHEN random() < 0.75 THEN 'Beauty'
                WHEN random() < 0.87 THEN 'Food & Drink'
                ELSE                      'Toys'
            END AS category,

            -- Quantity 1-12 (skewed low)
            LEAST(1 + floor(abs(random() * random()) * 12)::int, 12)  AS quantity,

            -- Unit price depends loosely on category via random bands
            round(
                CASE
                    WHEN random() < 0.2 THEN 150 + random() * 350   -- expensive
                    WHEN random() < 0.5 THEN 30 + random() * 120    -- mid-range
                    ELSE                      5 + random() * 45      -- cheap
                END, 2
            ) AS unit_price,

            -- Payment method
            CASE
                WHEN random() < 0.45 THEN 'Credit Card'
                WHEN random() < 0.70 THEN 'PayPal'
                WHEN random() < 0.85 THEN 'Apple Pay'
                ELSE                      'Bank Transfer'
            END AS payment_method,

            -- Shipping status
            CASE
                WHEN random() < 0.60 THEN 'Delivered'
                WHEN random() < 0.80 THEN 'Shipped'
                WHEN random() < 0.92 THEN 'Processing'
                ELSE                      'Returned'
            END AS status

        FROM generate_series(0, 1999) AS t(i)
    )
    SELECT order_date, order_id, region, category, quantity, unit_price,
           round(quantity * unit_price, 2) AS total, payment_method, status
    FROM raw
    ORDER BY order_date
]])

ds.log("Generated " .. ds.data.row_count .. " orders")
ds.log("Columns: " .. table.concat(ds.data.column_names, ", "))

-- Bar chart: total revenue by category
ds.chart.type  = "bar"
ds.chart.x     = 4      -- category
ds.chart.y     = {7}    -- total
ds.chart.title = "Order Totals by Category"

ds.log("=== Chart ready — switch to City View for 3D exploration ===")
ds.log("    Height: total | Color: category | District: region")