I'm sorry but this looks much better:<p><pre><code> 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;</code></pre>
Well, I prefer the polars version. And now if I want to reuse the CTEs elsewhere, I have to reach out to another tool like DBT or hand roll something to do string manipulation.
... does it? I don't think it does, even in this form.<p>And now write it such that all the conditions and transformations are injected into the string (somehow) rather than written in explicitly. Much worse.
Really? You've written out all the ratios and thresholds manually. If a user wanted to change the set of thresholds the polars way is far superior. In what way do you consider this better?