WITH raw_data AS (

    SELECT * FROM (
        VALUES 
            ('S01', 5000.0, 0.45, 120, 5),
            ('S02', 2400.0, 0.30,  45, 12),
            ('S03', 15000.0, 0.60, 300, 45),
            ('S04',  900.0, 0.15,  20, 2),
            ('S05', 3200.0, 0.50,  85, 8)
    ) AS t(store_id, revenue, margin, tx_count, returns)),

  base_data AS (
    SELECT
        store_id,
        revenue,
        margin,
        CAST(tx_count AS DOUBLE) AS tx_count,
        CAST(returns AS DOUBLE) AS returns
    FROM raw_data
  )

  SELECT

    store_id,
    revenue,
    margin,
    CAST(tx_count AS BIGINT) AS tx_count,
    CAST(returns AS BIGINT) AS returns,

    -- KPI Layer
    returns / tx_count AS return_rate,
    revenue / tx_count AS avg_order_value,

    -- Threshold Layer (matching original alias names)
    revenue > 1000.0 AS is_revenueabove1000,
    revenue > 5000.0 AS is_revenueabove5000,
    revenue > 10000.0 AS is_revenueabove10000,
    tx_count > 50 AS is_tx_countabove50,
    tx_count > 100 AS is_tx_countabove100,
    tx_count > 200 AS is_tx_countabove200,

    -- Interaction Layer (preserving exact numeric formula & aliases)
    revenue / (margin + 1e-5) AS ratio_revenuepermargin,
    revenue / (tx_count + 1e-5) AS ratio_revenuepertx_count,
    revenue / (returns + 1e-5) AS ratio_revenueperreturns,
    margin / (tx_count + 1e-5) AS ratio_marginpertx_count,
    margin / (returns + 1e-5) AS ratio_marginperreturns,
    tx_count / (returns + 1e-5) AS ratio_tx_countperreturns,

    -- Segmentation Layer
    CASE WHEN margin > 0.4 THEN 'High' ELSE 'Low' END AS margin_profile

  FROM base_data;