id stringlengths 13 13 | domain stringclasses 1
value | industry stringclasses 5
values | dialect stringclasses 5
values | complexity_archetype stringclasses 1
value | complexity_tier stringclasses 1
value | schema_context stringclasses 5
values | user_prompt stringlengths 138 246 | thought_chain stringlengths 233 523 | chosen_response stringlengths 923 1.62k | rejected_response stringlengths 102 187 | sql_query_clean stringlengths 451 1.09k | dpo_critique stringlengths 150 288 |
|---|---|---|---|---|---|---|---|---|---|---|---|---|
sql_sft_00001 | Enterprise SQL & Text-to-DB | Fintech & Core Banking | PostgreSQL 16 | Advanced Relational Window & CTE Synthesis | High | - accounts (account_id UUID PRIMARY KEY, user_id UUID, currency VARCHAR(3), balance NUMERIC(18,2), account_type VARCHAR(20), status VARCHAR(15), opened_at TIMESTAMPTZ)
- transactions (txn_id UUID PRIMARY KEY, source_acc_id UUID, dest_acc_id UUID, amount NUMERIC(18,2), fee NUMERIC(10,2), status VARCHAR(20), txn_type VAR... | In PostgreSQL 16, construct a high-performance SQL query to find the top 11 largest completed transactions for each active 'TREASURY' account over the last 63 days. Include transaction rank, account currency, amount, and timestamp. | <thought>
1. Identify target tables: `accounts` (filtering `status = 'ACTIVE'`, `account_type = 'TREASURY'`), and `transactions`.
2. Time window: Native PostgreSQL 16 syntax for 63 days: `t.created_at >= CURRENT_TIMESTAMP - INTERVAL '63 days'`.
3. Status filter: Ensure `transactions.status = 'COMPLETED'`.
4. Window Ran... | <thought>
1. Identify target tables: `accounts` (filtering `status = 'ACTIVE'`, `account_type = 'TREASURY'`), and `transactions`.
2. Time window: Native PostgreSQL 16 syntax for 63 days: `t.created_at >= CURRENT_TIMESTAMP - INTERVAL '63 days'`.
3. Status filter: Ensure `transactions.status = 'COMPLETED'`.
4. Window Ran... | ```sql
SELECT a.account_id, t.txn_id, t.amount
FROM accounts a, transactions t
WHERE a.account_id = t.source_acc_id AND a.account_type = 'TREASURY'
ORDER BY t.amount DESC LIMIT 11;
``` | WITH ranked_txns AS (
SELECT
a.account_id, a.user_id, a.currency, t.txn_id, t.amount, t.created_at,
DENSE_RANK() OVER (PARTITION BY a.account_id ORDER BY t.amount DESC, t.created_at DESC) AS txn_rank
FROM accounts a
INNER JOIN transactions t ON a.account_id = t.source_acc_id
WHERE a.sta... | Chosen uses native PostgreSQL 16 window partitioning to get top 11 per individual account; Rejected applies an unpartitioned global LIMIT across all accounts combined. |
sql_sft_00002 | Enterprise SQL & Text-to-DB | SaaS & Subscription Analytics | PostgreSQL 16 | Advanced Relational Window & CTE Synthesis | High | - workspaces (workspace_id UUID PRIMARY KEY, owner_id UUID, company_name VARCHAR(100), plan_tier VARCHAR(20), mrr NUMERIC(10,2), seats_allocated INT, created_at TIMESTAMPTZ)
- subscription_invoices (invoice_id UUID PRIMARY KEY, workspace_id UUID, amount_billed NUMERIC(10,2), payment_status VARCHAR(20), billing_period_s... | In PostgreSQL 16, compose an optimized query returning 'SCALE' workspaces with at least 145 allocated seats where average user event activity has dropped by more than 50% over the last 128 days compared to prior period. | <thought>
1. Tables: `workspaces`, `user_activity_events`.
2. Filter: `workspaces.plan_tier = 'SCALE'` and `seats_allocated >= 145`.
3. Compare two time windows: `e.created_at >= CURRENT_TIMESTAMP - INTERVAL '128 days'` vs prior period.
</thought> | <thought>
1. Tables: `workspaces`, `user_activity_events`.
2. Filter: `workspaces.plan_tier = 'SCALE'` and `seats_allocated >= 145`.
3. Compare two time windows: `e.created_at >= CURRENT_TIMESTAMP - INTERVAL '128 days'` vs prior period.
</thought>
```sql
WITH activity_summary AS (
SELECT
w.workspace_id,
... | ```sql
SELECT w.workspace_id, COUNT(e.event_id)
FROM workspaces w, user_activity_events e
WHERE w.workspace_id = e.workspace_id
GROUP BY w.workspace_id;
``` | WITH activity_summary AS (
SELECT
w.workspace_id,
w.company_name,
w.seats_allocated,
w.mrr,
COUNT(CASE WHEN e.created_at >= CURRENT_TIMESTAMP - INTERVAL '128 days' THEN 1 END) AS current_events,
COUNT(CASE WHEN e.created_at >= CURRENT_TIMESTAMP - INTERVAL '256 days' ... | Chosen uses native PostgreSQL 16 conditional aggregation to compare historical time periods with NULLIF division protection; Rejected does an unconstrained join without date windows. |
sql_sft_00003 | Enterprise SQL & Text-to-DB | E-Commerce & Global Retail | PostgreSQL 16 | Advanced Relational Window & CTE Synthesis | High | - customers (customer_id UUID PRIMARY KEY, email VARCHAR(150), country VARCHAR(2), loyalty_tier VARCHAR(20), lifetime_spend NUMERIC(12,2), first_order_at TIMESTAMPTZ, is_active BOOL)
- orders (order_id UUID PRIMARY KEY, customer_id UUID, status VARCHAR(20), gross_total NUMERIC(12,2), discount_amount NUMERIC(10,2), net_... | Using PostgreSQL 16, write a window-partitioned query to determine all 'TIER_1' tier customers in 'IT' whose 300-day repeat purchase velocity exceeds 36151 USD, and list their total net spent. | <thought>
1. Tables: `customers`, `orders`.
2. Filters: `country = 'IT'`, `loyalty_tier = 'TIER_1'`, `orders.status IN ('DELIVERED', 'COMPLETED')`.
3. Date Filter (PostgreSQL 16): `o.placed_at >= CURRENT_TIMESTAMP - INTERVAL '300 days'`.
4. Group by customer, calculate total net spend and order count.
</thought> | <thought>
1. Tables: `customers`, `orders`.
2. Filters: `country = 'IT'`, `loyalty_tier = 'TIER_1'`, `orders.status IN ('DELIVERED', 'COMPLETED')`.
3. Date Filter (PostgreSQL 16): `o.placed_at >= CURRENT_TIMESTAMP - INTERVAL '300 days'`.
4. Group by customer, calculate total net spend and order count.
</thought>
```sq... | ```sql
SELECT c.customer_id, SUM(o.gross_total)
FROM customers c
JOIN orders o ON c.customer_id = o.customer_id
WHERE c.country = 'IT'
GROUP BY c.customer_id;
``` | WITH customer_spends AS (
SELECT
c.customer_id,
c.email,
COUNT(DISTINCT o.order_id) AS total_orders,
SUM(o.net_total) AS total_net_spent
FROM customers c
INNER JOIN orders o ON c.customer_id = o.customer_id
WHERE c.country = 'IT'
AND c.loyalty_tier = 'TIER_1'
... | Chosen filters by delivered orders and computes net revenue within native PostgreSQL 16 timeframe (o.placed_at >= CURRENT_TIMESTAMP - INTERVAL '300 days'); Rejected sums gross revenue without status or date constraints. |
sql_sft_00004 | Enterprise SQL & Text-to-DB | Healthcare & Hospital Management | PostgreSQL 16 | Advanced Relational Window & CTE Synthesis | High | - patients (patient_id UUID PRIMARY KEY, mrn VARCHAR(20), birth_date DATE, gender VARCHAR(10), blood_type VARCHAR(5), primary_physician_id UUID, created_at TIMESTAMPTZ)
- clinical_encounters (encounter_id UUID PRIMARY KEY, patient_id UUID, department VARCHAR(50), admission_time TIMESTAMPTZ, discharge_time TIMESTAMPTZ, ... | In PostgreSQL 16, construct a high-performance SQL query to find the 29-day all-cause readmission rate and median length of stay in days for the 'CARDIOLOGY' department across all inpatient encounters in 2024. | <thought>
1. Tables: `clinical_encounters`.
2. Department filter: `department = 'CARDIOLOGY'`.
3. Length of stay: `EXTRACT(EPOCH FROM (discharge_time - admission_time)) / 86400.0`.
4. Readmission: `LEAD(admission_time) OVER (PARTITION BY patient_id ORDER BY admission_time)`.
5. Median percentile in PostgreSQL 16: `PERC... | <thought>
1. Tables: `clinical_encounters`.
2. Department filter: `department = 'CARDIOLOGY'`.
3. Length of stay: `EXTRACT(EPOCH FROM (discharge_time - admission_time)) / 86400.0`.
4. Readmission: `LEAD(admission_time) OVER (PARTITION BY patient_id ORDER BY admission_time)`.
5. Median percentile in PostgreSQL 16: `PERC... | ```sql
SELECT department, COUNT(encounter_id), AVG(discharge_time - admission_time)
FROM clinical_encounters WHERE department = 'CARDIOLOGY' GROUP BY department;
``` | WITH encounter_timelines AS (
SELECT
encounter_id,
patient_id,
admission_time,
discharge_time,
EXTRACT(EPOCH FROM (discharge_time - admission_time)) / 86400.0 AS los_days,
LEAD(admission_time) OVER (
PARTITION BY patient_id
ORDER BY admission... | Chosen accurately tracks patient readmissions via LEAD() within a 29-day window and uses native PostgreSQL 16 median calculation (PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY los_days)); Rejected provides unadjusted averages without readmission tracking. |
sql_sft_00005 | Enterprise SQL & Text-to-DB | Logistics & Global Supply Chain | PostgreSQL 16 | Advanced Relational Window & CTE Synthesis | High | - shipments (shipment_id UUID PRIMARY KEY, origin_hub_id UUID, dest_hub_id UUID, carrier_id UUID, status VARCHAR(25), weight_kg NUMERIC(10,2), scheduled_delivery TIMESTAMPTZ, actual_delivery TIMESTAMPTZ)
- tracking_milestones (milestone_id UUID PRIMARY KEY, shipment_id UUID, checkpoint_name VARCHAR(100), latitude NUMER... | Using PostgreSQL 16, write an index-optimized query to retrieve logistics carriers handling shipments over 3733 kg whose actual On-Time Delivery (OTD) percentage fell below the contractual SLA target of 97.0% over the last 87 days. | <thought>
1. Tables: `shipments`, `carrier_sla_benchmarks`.
2. Filter: `weight_kg >= 3733`, status = 'DELIVERED'.
3. Time filter (PostgreSQL 16): `s.scheduled_delivery >= CURRENT_TIMESTAMP - INTERVAL '87 days'`.
4. OTD: `actual_delivery <= scheduled_delivery`.
5. Group by carrier and calculate adherence percentage.
</t... | <thought>
1. Tables: `shipments`, `carrier_sla_benchmarks`.
2. Filter: `weight_kg >= 3733`, status = 'DELIVERED'.
3. Time filter (PostgreSQL 16): `s.scheduled_delivery >= CURRENT_TIMESTAMP - INTERVAL '87 days'`.
4. OTD: `actual_delivery <= scheduled_delivery`.
5. Group by carrier and calculate adherence percentage.
</t... | ```sql
SELECT carrier_id, AVG(actual_delivery - scheduled_delivery)
FROM shipments WHERE weight_kg >= 3733 GROUP BY carrier_id;
``` | WITH carrier_perf AS (
SELECT
s.carrier_id,
COUNT(s.shipment_id) AS total_shipments,
SUM(CASE WHEN s.actual_delivery <= s.scheduled_delivery THEN 1 ELSE 0 END) AS on_time_count,
ROUND((SUM(CASE WHEN s.actual_delivery <= s.scheduled_delivery THEN 1.0 ELSE 0.0 END) /
N... | Chosen calculates exact OTD compliance percentage against SLA benchmarks with native PostgreSQL 16 date filter (s.scheduled_delivery >= CURRENT_TIMESTAMP - INTERVAL '87 days') and NULLIF protection; Rejected uses raw timestamp differences without SLA threshold comparison. |
sql_sft_00006 | Enterprise SQL & Text-to-DB | Fintech & Core Banking | Snowflake | Advanced Relational Window & CTE Synthesis | High | - accounts (account_id UUID PRIMARY KEY, user_id UUID, currency VARCHAR(3), balance NUMERIC(18,2), account_type VARCHAR(20), status VARCHAR(15), opened_at TIMESTAMPTZ)
- transactions (txn_id UUID PRIMARY KEY, source_acc_id UUID, dest_acc_id UUID, amount NUMERIC(18,2), fee NUMERIC(10,2), status VARCHAR(20), txn_type VAR... | Using Snowflake, calculate the exact breakdown of the daily net transaction inflow for currency 'CAD' over the last 353 days. Filter out any transactions flagged with an AML risk score exceeding 77. | <thought>
1. Table selection: `transactions` with left joins to `accounts` for currency verification.
2. Inflow vs Outflow: Inflow is incoming (dest_acc_id), Outflow is outgoing (source_acc_id).
3. AML Exclusion: Use `NOT EXISTS` against `aml_screenings` with `risk_score > 77` to avoid dropping unscreened transactions.... | <thought>
1. Table selection: `transactions` with left joins to `accounts` for currency verification.
2. Inflow vs Outflow: Inflow is incoming (dest_acc_id), Outflow is outgoing (source_acc_id).
3. AML Exclusion: Use `NOT EXISTS` against `aml_screenings` with `risk_score > 77` to avoid dropping unscreened transactions.... | ```sql
SELECT CAST(t.created_at AS DATE), SUM(t.amount)
FROM transactions t
INNER JOIN aml_screenings s ON t.txn_id = s.txn_id
WHERE s.risk_score <= 77
GROUP BY 1;
``` | SELECT
CAST(t.created_at AS DATE) AS txn_date,
SUM(CASE WHEN a_dest.currency = 'CAD' THEN t.amount ELSE 0 END) AS total_inflow,
SUM(CASE WHEN a_src.currency = 'CAD' THEN t.amount ELSE 0 END) AS total_outflow,
SUM(CASE WHEN a_dest.currency = 'CAD' THEN t.amount ELSE 0 END) -
SUM(CASE WHEN a_src.cur... | Chosen uses NOT EXISTS to keep clean unscreened transactions and separates inflow from outflow; Rejected uses INNER JOIN which drops all clean transactions that haven't been manually screened. |
sql_sft_00007 | Enterprise SQL & Text-to-DB | SaaS & Subscription Analytics | Snowflake | Advanced Relational Window & CTE Synthesis | High | - workspaces (workspace_id UUID PRIMARY KEY, owner_id UUID, company_name VARCHAR(100), plan_tier VARCHAR(20), mrr NUMERIC(10,2), seats_allocated INT, created_at TIMESTAMPTZ)
- subscription_invoices (invoice_id UUID PRIMARY KEY, workspace_id UUID, amount_billed NUMERIC(10,2), payment_status VARCHAR(20), billing_period_s... | Using Snowflake, construct a high-performance SQL query to find the Month-over-Month MRR expansion and net contraction for 'UNLIMITED' workspaces over the last 196 days, using paid invoices. | <thought>
1. Tables: `workspaces` (filtering `plan_tier = 'UNLIMITED'`), `subscription_invoices` (`payment_status = 'PAID'`).
2. Monthly revenue aggregation in Snowflake: `DATE_TRUNC('month', i.billing_period_start)`.
3. Time filter in Snowflake: `i.billing_period_start >= DATEADD(day, -196, CURRENT_TIMESTAMP())`.
4. C... | <thought>
1. Tables: `workspaces` (filtering `plan_tier = 'UNLIMITED'`), `subscription_invoices` (`payment_status = 'PAID'`).
2. Monthly revenue aggregation in Snowflake: `DATE_TRUNC('month', i.billing_period_start)`.
3. Time filter in Snowflake: `i.billing_period_start >= DATEADD(day, -196, CURRENT_TIMESTAMP())`.
4. C... | ```sql
SELECT DATE_TRUNC('month', i.billing_period_start), SUM(amount_billed)
FROM subscription_invoices WHERE payment_status = 'PAID' GROUP BY 1;
``` | WITH monthly_workspace_rev AS (
SELECT
w.workspace_id,
w.company_name,
DATE_TRUNC('month', i.billing_period_start) AS billing_month,
SUM(i.amount_billed) AS current_mrr,
LAG(SUM(i.amount_billed)) OVER (
PARTITION BY w.workspace_id
ORDER BY DATE_TRUNC... | Chosen leverages native Snowflake date truncation (DATE_TRUNC('month', i.billing_period_start)) and LAG() over cohorts; Rejected merges all revenue without cohort delta tracking. |
sql_sft_00008 | Enterprise SQL & Text-to-DB | E-Commerce & Global Retail | Snowflake | Advanced Relational Window & CTE Synthesis | High | - customers (customer_id UUID PRIMARY KEY, email VARCHAR(150), country VARCHAR(2), loyalty_tier VARCHAR(20), lifetime_spend NUMERIC(12,2), first_order_at TIMESTAMPTZ, is_active BOOL)
- orders (order_id UUID PRIMARY KEY, customer_id UUID, status VARCHAR(20), gross_total NUMERIC(12,2), discount_amount NUMERIC(10,2), net_... | Using Snowflake, develop a schema-aware query to isolate all 'PREMIUM' tier customers in 'ES' whose 181-day repeat purchase velocity exceeds 6126 USD, and list their total net spent. | <thought>
1. Tables: `customers`, `orders`.
2. Filters: `country = 'ES'`, `loyalty_tier = 'PREMIUM'`, `orders.status IN ('DELIVERED', 'COMPLETED')`.
3. Date Filter (Snowflake): `o.placed_at >= DATEADD(day, -181, CURRENT_TIMESTAMP())`.
4. Group by customer, calculate total net spend and order count.
</thought> | <thought>
1. Tables: `customers`, `orders`.
2. Filters: `country = 'ES'`, `loyalty_tier = 'PREMIUM'`, `orders.status IN ('DELIVERED', 'COMPLETED')`.
3. Date Filter (Snowflake): `o.placed_at >= DATEADD(day, -181, CURRENT_TIMESTAMP())`.
4. Group by customer, calculate total net spend and order count.
</thought>
```sql
W... | ```sql
SELECT c.customer_id, SUM(o.gross_total)
FROM customers c
JOIN orders o ON c.customer_id = o.customer_id
WHERE c.country = 'ES'
GROUP BY c.customer_id;
``` | WITH customer_spends AS (
SELECT
c.customer_id,
c.email,
COUNT(DISTINCT o.order_id) AS total_orders,
SUM(o.net_total) AS total_net_spent
FROM customers c
INNER JOIN orders o ON c.customer_id = o.customer_id
WHERE c.country = 'ES'
AND c.loyalty_tier = 'PREMIUM'
... | Chosen filters by delivered orders and computes net revenue within native Snowflake timeframe (o.placed_at >= DATEADD(day, -181, CURRENT_TIMESTAMP())); Rejected sums gross revenue without status or date constraints. |
sql_sft_00009 | Enterprise SQL & Text-to-DB | Healthcare & Hospital Management | Snowflake | Advanced Relational Window & CTE Synthesis | High | - patients (patient_id UUID PRIMARY KEY, mrn VARCHAR(20), birth_date DATE, gender VARCHAR(10), blood_type VARCHAR(5), primary_physician_id UUID, created_at TIMESTAMPTZ)
- clinical_encounters (encounter_id UUID PRIMARY KEY, patient_id UUID, department VARCHAR(50), admission_time TIMESTAMPTZ, discharge_time TIMESTAMPTZ, ... | In Snowflake, generate a production-ready analytical query to compute the 32-day all-cause readmission rate and median length of stay in days for the 'PULMONOLOGY' department across all inpatient encounters in 2026. | <thought>
1. Tables: `clinical_encounters`.
2. Department filter: `department = 'PULMONOLOGY'`.
3. Length of stay: `EXTRACT(EPOCH FROM (discharge_time - admission_time)) / 86400.0`.
4. Readmission: `LEAD(admission_time) OVER (PARTITION BY patient_id ORDER BY admission_time)`.
5. Median percentile in Snowflake: `PERCENT... | <thought>
1. Tables: `clinical_encounters`.
2. Department filter: `department = 'PULMONOLOGY'`.
3. Length of stay: `EXTRACT(EPOCH FROM (discharge_time - admission_time)) / 86400.0`.
4. Readmission: `LEAD(admission_time) OVER (PARTITION BY patient_id ORDER BY admission_time)`.
5. Median percentile in Snowflake: `PERCENT... | ```sql
SELECT department, COUNT(encounter_id), AVG(discharge_time - admission_time)
FROM clinical_encounters WHERE department = 'PULMONOLOGY' GROUP BY department;
``` | WITH encounter_timelines AS (
SELECT
encounter_id,
patient_id,
admission_time,
discharge_time,
EXTRACT(EPOCH FROM (discharge_time - admission_time)) / 86400.0 AS los_days,
LEAD(admission_time) OVER (
PARTITION BY patient_id
ORDER BY admission... | Chosen accurately tracks patient readmissions via LEAD() within a 32-day window and uses native Snowflake median calculation (PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY los_days)); Rejected provides unadjusted averages without readmission tracking. |
sql_sft_00010 | Enterprise SQL & Text-to-DB | Logistics & Global Supply Chain | Snowflake | Advanced Relational Window & CTE Synthesis | High | - shipments (shipment_id UUID PRIMARY KEY, origin_hub_id UUID, dest_hub_id UUID, carrier_id UUID, status VARCHAR(25), weight_kg NUMERIC(10,2), scheduled_delivery TIMESTAMPTZ, actual_delivery TIMESTAMPTZ)
- tracking_milestones (milestone_id UUID PRIMARY KEY, shipment_id UUID, checkpoint_name VARCHAR(100), latitude NUMER... | Using Snowflake, calculate the exact breakdown of logistics carriers handling shipments over 3071 kg whose actual On-Time Delivery (OTD) percentage fell below the contractual SLA target of 91.4% over the last 161 days. | <thought>
1. Tables: `shipments`, `carrier_sla_benchmarks`.
2. Filter: `weight_kg >= 3071`, status = 'DELIVERED'.
3. Time filter (Snowflake): `s.scheduled_delivery >= DATEADD(day, -161, CURRENT_TIMESTAMP())`.
4. OTD: `actual_delivery <= scheduled_delivery`.
5. Group by carrier and calculate adherence percentage.
</thou... | <thought>
1. Tables: `shipments`, `carrier_sla_benchmarks`.
2. Filter: `weight_kg >= 3071`, status = 'DELIVERED'.
3. Time filter (Snowflake): `s.scheduled_delivery >= DATEADD(day, -161, CURRENT_TIMESTAMP())`.
4. OTD: `actual_delivery <= scheduled_delivery`.
5. Group by carrier and calculate adherence percentage.
</thou... | ```sql
SELECT carrier_id, AVG(actual_delivery - scheduled_delivery)
FROM shipments WHERE weight_kg >= 3071 GROUP BY carrier_id;
``` | WITH carrier_perf AS (
SELECT
s.carrier_id,
COUNT(s.shipment_id) AS total_shipments,
SUM(CASE WHEN s.actual_delivery <= s.scheduled_delivery THEN 1 ELSE 0 END) AS on_time_count,
ROUND((SUM(CASE WHEN s.actual_delivery <= s.scheduled_delivery THEN 1.0 ELSE 0.0 END) /
N... | Chosen calculates exact OTD compliance percentage against SLA benchmarks with native Snowflake date filter (s.scheduled_delivery >= DATEADD(day, -161, CURRENT_TIMESTAMP())) and NULLIF protection; Rejected uses raw timestamp differences without SLA threshold comparison. |
sql_sft_00011 | Enterprise SQL & Text-to-DB | Fintech & Core Banking | ClickHouse (OLAP) | Advanced Relational Window & CTE Synthesis | High | - accounts (account_id UUID PRIMARY KEY, user_id UUID, currency VARCHAR(3), balance NUMERIC(18,2), account_type VARCHAR(20), status VARCHAR(15), opened_at TIMESTAMPTZ)
- transactions (txn_id UUID PRIMARY KEY, source_acc_id UUID, dest_acc_id UUID, amount NUMERIC(18,2), fee NUMERIC(10,2), status VARCHAR(20), txn_type VAR... | In ClickHouse (OLAP), compute the 7-day rolling moving average balance and cumulative volume for accounts exceeding a closing balance of 52768 EUR. | <thought>
1. Table: `daily_balance_snapshots` joined with `accounts`.
2. Window: `AVG(closing_balance) OVER (PARTITION BY account_id ORDER BY snapshot_date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW)`.
3. Filter by currency 'EUR' and closing balance >= 52768.
</thought> | <thought>
1. Table: `daily_balance_snapshots` joined with `accounts`.
2. Window: `AVG(closing_balance) OVER (PARTITION BY account_id ORDER BY snapshot_date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW)`.
3. Filter by currency 'EUR' and closing balance >= 52768.
</thought>
```sql
SELECT
s.account_id,
s.snapshot_da... | ```sql
SELECT account_id, AVG(closing_balance)
FROM daily_balance_snapshots
GROUP BY account_id;
``` | SELECT
s.account_id,
s.snapshot_date,
s.closing_balance,
AVG(s.closing_balance) OVER (
PARTITION BY s.account_id
ORDER BY s.snapshot_date
ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
) AS rolling_7d_avg_balance,
SUM(s.total_inflow + s.total_outflow) OVER (
PARTITIO... | Chosen uses a sliding window frame (ROWS BETWEEN 6 PRECEDING AND CURRENT ROW); Rejected collapses all time series into a single static scalar average. |
sql_sft_00012 | Enterprise SQL & Text-to-DB | SaaS & Subscription Analytics | ClickHouse (OLAP) | Advanced Relational Window & CTE Synthesis | High | - workspaces (workspace_id UUID PRIMARY KEY, owner_id UUID, company_name VARCHAR(100), plan_tier VARCHAR(20), mrr NUMERIC(10,2), seats_allocated INT, created_at TIMESTAMPTZ)
- subscription_invoices (invoice_id UUID PRIMARY KEY, workspace_id UUID, amount_billed NUMERIC(10,2), payment_status VARCHAR(20), billing_period_s... | In ClickHouse (OLAP), write a window-partitioned query to determine 'ENTERPRISE' workspaces with at least 195 allocated seats where average user event activity has dropped by more than 50% over the last 73 days compared to prior period. | <thought>
1. Tables: `workspaces`, `user_activity_events`.
2. Filter: `workspaces.plan_tier = 'ENTERPRISE'` and `seats_allocated >= 195`.
3. Compare two time windows: `e.created_at >= subtractDays(now(), 73)` vs prior period.
</thought> | <thought>
1. Tables: `workspaces`, `user_activity_events`.
2. Filter: `workspaces.plan_tier = 'ENTERPRISE'` and `seats_allocated >= 195`.
3. Compare two time windows: `e.created_at >= subtractDays(now(), 73)` vs prior period.
</thought>
```sql
WITH activity_summary AS (
SELECT
w.workspace_id,
w.co... | ```sql
SELECT w.workspace_id, COUNT(e.event_id)
FROM workspaces w, user_activity_events e
WHERE w.workspace_id = e.workspace_id
GROUP BY w.workspace_id;
``` | WITH activity_summary AS (
SELECT
w.workspace_id,
w.company_name,
w.seats_allocated,
w.mrr,
COUNT(CASE WHEN e.created_at >= subtractDays(now(), 73) THEN 1 END) AS current_events,
COUNT(CASE WHEN e.created_at >= subtractDays(now(), 146) AND NOT (e.created_at >= subtra... | Chosen uses native ClickHouse (OLAP) conditional aggregation to compare historical time periods with NULLIF division protection; Rejected does an unconstrained join without date windows. |
sql_sft_00013 | Enterprise SQL & Text-to-DB | E-Commerce & Global Retail | ClickHouse (OLAP) | Advanced Relational Window & CTE Synthesis | High | - customers (customer_id UUID PRIMARY KEY, email VARCHAR(150), country VARCHAR(2), loyalty_tier VARCHAR(20), lifetime_spend NUMERIC(12,2), first_order_at TIMESTAMPTZ, is_active BOOL)
- orders (order_id UUID PRIMARY KEY, customer_id UUID, status VARCHAR(20), gross_total NUMERIC(12,2), discount_amount NUMERIC(10,2), net_... | Using ClickHouse (OLAP), write a window-partitioned query to determine all 'SILVER' tier customers in 'AU' whose 210-day repeat purchase velocity exceeds 34633 USD, and list their total net spent. | <thought>
1. Tables: `customers`, `orders`.
2. Filters: `country = 'AU'`, `loyalty_tier = 'SILVER'`, `orders.status IN ('DELIVERED', 'COMPLETED')`.
3. Date Filter (ClickHouse (OLAP)): `o.placed_at >= subtractDays(now(), 210)`.
4. Group by customer, calculate total net spend and order count.
</thought> | <thought>
1. Tables: `customers`, `orders`.
2. Filters: `country = 'AU'`, `loyalty_tier = 'SILVER'`, `orders.status IN ('DELIVERED', 'COMPLETED')`.
3. Date Filter (ClickHouse (OLAP)): `o.placed_at >= subtractDays(now(), 210)`.
4. Group by customer, calculate total net spend and order count.
</thought>
```sql
WITH cust... | ```sql
SELECT c.customer_id, SUM(o.gross_total)
FROM customers c
JOIN orders o ON c.customer_id = o.customer_id
WHERE c.country = 'AU'
GROUP BY c.customer_id;
``` | WITH customer_spends AS (
SELECT
c.customer_id,
c.email,
COUNT(DISTINCT o.order_id) AS total_orders,
SUM(o.net_total) AS total_net_spent
FROM customers c
INNER JOIN orders o ON c.customer_id = o.customer_id
WHERE c.country = 'AU'
AND c.loyalty_tier = 'SILVER'
... | Chosen filters by delivered orders and computes net revenue within native ClickHouse (OLAP) timeframe (o.placed_at >= subtractDays(now(), 210)); Rejected sums gross revenue without status or date constraints. |
sql_sft_00014 | Enterprise SQL & Text-to-DB | Healthcare & Hospital Management | ClickHouse (OLAP) | Advanced Relational Window & CTE Synthesis | High | - patients (patient_id UUID PRIMARY KEY, mrn VARCHAR(20), birth_date DATE, gender VARCHAR(10), blood_type VARCHAR(5), primary_physician_id UUID, created_at TIMESTAMPTZ)
- clinical_encounters (encounter_id UUID PRIMARY KEY, patient_id UUID, department VARCHAR(50), admission_time TIMESTAMPTZ, discharge_time TIMESTAMPTZ, ... | In ClickHouse (OLAP), write a CTE-based SQL query to extract the 46-day all-cause readmission rate and median length of stay in days for the 'CARDIOLOGY' department across all inpatient encounters in 2024. | <thought>
1. Tables: `clinical_encounters`.
2. Department filter: `department = 'CARDIOLOGY'`.
3. Length of stay: `EXTRACT(EPOCH FROM (discharge_time - admission_time)) / 86400.0`.
4. Readmission: `LEAD(admission_time) OVER (PARTITION BY patient_id ORDER BY admission_time)`.
5. Median percentile in ClickHouse (OLAP): `... | <thought>
1. Tables: `clinical_encounters`.
2. Department filter: `department = 'CARDIOLOGY'`.
3. Length of stay: `EXTRACT(EPOCH FROM (discharge_time - admission_time)) / 86400.0`.
4. Readmission: `LEAD(admission_time) OVER (PARTITION BY patient_id ORDER BY admission_time)`.
5. Median percentile in ClickHouse (OLAP): `... | ```sql
SELECT department, COUNT(encounter_id), AVG(discharge_time - admission_time)
FROM clinical_encounters WHERE department = 'CARDIOLOGY' GROUP BY department;
``` | WITH encounter_timelines AS (
SELECT
encounter_id,
patient_id,
admission_time,
discharge_time,
EXTRACT(EPOCH FROM (discharge_time - admission_time)) / 86400.0 AS los_days,
LEAD(admission_time) OVER (
PARTITION BY patient_id
ORDER BY admission... | Chosen accurately tracks patient readmissions via LEAD() within a 46-day window and uses native ClickHouse (OLAP) median calculation (quantile(0.5)(los_days)); Rejected provides unadjusted averages without readmission tracking. |
sql_sft_00015 | Enterprise SQL & Text-to-DB | Logistics & Global Supply Chain | ClickHouse (OLAP) | Advanced Relational Window & CTE Synthesis | High | - shipments (shipment_id UUID PRIMARY KEY, origin_hub_id UUID, dest_hub_id UUID, carrier_id UUID, status VARCHAR(25), weight_kg NUMERIC(10,2), scheduled_delivery TIMESTAMPTZ, actual_delivery TIMESTAMPTZ)
- tracking_milestones (milestone_id UUID PRIMARY KEY, shipment_id UUID, checkpoint_name VARCHAR(100), latitude NUMER... | Using ClickHouse (OLAP), construct a high-performance SQL query to find logistics carriers handling shipments over 4336 kg whose actual On-Time Delivery (OTD) percentage fell below the contractual SLA target of 98.6% over the last 26 days. | <thought>
1. Tables: `shipments`, `carrier_sla_benchmarks`.
2. Filter: `weight_kg >= 4336`, status = 'DELIVERED'.
3. Time filter (ClickHouse (OLAP)): `s.scheduled_delivery >= subtractDays(now(), 26)`.
4. OTD: `actual_delivery <= scheduled_delivery`.
5. Group by carrier and calculate adherence percentage.
</thought> | <thought>
1. Tables: `shipments`, `carrier_sla_benchmarks`.
2. Filter: `weight_kg >= 4336`, status = 'DELIVERED'.
3. Time filter (ClickHouse (OLAP)): `s.scheduled_delivery >= subtractDays(now(), 26)`.
4. OTD: `actual_delivery <= scheduled_delivery`.
5. Group by carrier and calculate adherence percentage.
</thought>
``... | ```sql
SELECT carrier_id, AVG(actual_delivery - scheduled_delivery)
FROM shipments WHERE weight_kg >= 4336 GROUP BY carrier_id;
``` | WITH carrier_perf AS (
SELECT
s.carrier_id,
COUNT(s.shipment_id) AS total_shipments,
SUM(CASE WHEN s.actual_delivery <= s.scheduled_delivery THEN 1 ELSE 0 END) AS on_time_count,
ROUND((SUM(CASE WHEN s.actual_delivery <= s.scheduled_delivery THEN 1.0 ELSE 0.0 END) /
N... | Chosen calculates exact OTD compliance percentage against SLA benchmarks with native ClickHouse (OLAP) date filter (s.scheduled_delivery >= subtractDays(now(), 26)) and NULLIF protection; Rejected uses raw timestamp differences without SLA threshold comparison. |
sql_sft_00016 | Enterprise SQL & Text-to-DB | Fintech & Core Banking | MySQL 8.0 | Advanced Relational Window & CTE Synthesis | High | - accounts (account_id UUID PRIMARY KEY, user_id UUID, currency VARCHAR(3), balance NUMERIC(18,2), account_type VARCHAR(20), status VARCHAR(15), opened_at TIMESTAMPTZ)
- transactions (txn_id UUID PRIMARY KEY, source_acc_id UUID, dest_acc_id UUID, amount NUMERIC(18,2), fee NUMERIC(10,2), status VARCHAR(20), txn_type VAR... | In MySQL 8.0, compute the 7-day rolling moving average balance and cumulative volume for accounts exceeding a closing balance of 19166 CHF. | <thought>
1. Table: `daily_balance_snapshots` joined with `accounts`.
2. Window: `AVG(closing_balance) OVER (PARTITION BY account_id ORDER BY snapshot_date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW)`.
3. Filter by currency 'CHF' and closing balance >= 19166.
</thought> | <thought>
1. Table: `daily_balance_snapshots` joined with `accounts`.
2. Window: `AVG(closing_balance) OVER (PARTITION BY account_id ORDER BY snapshot_date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW)`.
3. Filter by currency 'CHF' and closing balance >= 19166.
</thought>
```sql
SELECT
s.account_id,
s.snapshot_da... | ```sql
SELECT account_id, AVG(closing_balance)
FROM daily_balance_snapshots
GROUP BY account_id;
``` | SELECT
s.account_id,
s.snapshot_date,
s.closing_balance,
AVG(s.closing_balance) OVER (
PARTITION BY s.account_id
ORDER BY s.snapshot_date
ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
) AS rolling_7d_avg_balance,
SUM(s.total_inflow + s.total_outflow) OVER (
PARTITIO... | Chosen uses a sliding window frame (ROWS BETWEEN 6 PRECEDING AND CURRENT ROW); Rejected collapses all time series into a single static scalar average. |
sql_sft_00017 | Enterprise SQL & Text-to-DB | SaaS & Subscription Analytics | MySQL 8.0 | Advanced Relational Window & CTE Synthesis | High | - workspaces (workspace_id UUID PRIMARY KEY, owner_id UUID, company_name VARCHAR(100), plan_tier VARCHAR(20), mrr NUMERIC(10,2), seats_allocated INT, created_at TIMESTAMPTZ)
- subscription_invoices (invoice_id UUID PRIMARY KEY, workspace_id UUID, amount_billed NUMERIC(10,2), payment_status VARCHAR(20), billing_period_s... | Using MySQL 8.0, write a window-partitioned query to determine the Month-over-Month MRR expansion and net contraction for 'GROWTH' workspaces over the last 334 days, using paid invoices. | <thought>
1. Tables: `workspaces` (filtering `plan_tier = 'GROWTH'`), `subscription_invoices` (`payment_status = 'PAID'`).
2. Monthly revenue aggregation in MySQL 8.0: `DATE_FORMAT(i.billing_period_start, '%Y-%m-01')`.
3. Time filter in MySQL 8.0: `i.billing_period_start >= DATE_SUB(NOW(), INTERVAL 334 DAY)`.
4. Cohort... | <thought>
1. Tables: `workspaces` (filtering `plan_tier = 'GROWTH'`), `subscription_invoices` (`payment_status = 'PAID'`).
2. Monthly revenue aggregation in MySQL 8.0: `DATE_FORMAT(i.billing_period_start, '%Y-%m-01')`.
3. Time filter in MySQL 8.0: `i.billing_period_start >= DATE_SUB(NOW(), INTERVAL 334 DAY)`.
4. Cohort... | ```sql
SELECT DATE_FORMAT(i.billing_period_start, '%Y-%m-01'), SUM(amount_billed)
FROM subscription_invoices WHERE payment_status = 'PAID' GROUP BY 1;
``` | WITH monthly_workspace_rev AS (
SELECT
w.workspace_id,
w.company_name,
DATE_FORMAT(i.billing_period_start, '%Y-%m-01') AS billing_month,
SUM(i.amount_billed) AS current_mrr,
LAG(SUM(i.amount_billed)) OVER (
PARTITION BY w.workspace_id
ORDER BY DATE_F... | Chosen leverages native MySQL 8.0 date truncation (DATE_FORMAT(i.billing_period_start, '%Y-%m-01')) and LAG() over cohorts; Rejected merges all revenue without cohort delta tracking. |
sql_sft_00018 | Enterprise SQL & Text-to-DB | E-Commerce & Global Retail | MySQL 8.0 | Advanced Relational Window & CTE Synthesis | High | - customers (customer_id UUID PRIMARY KEY, email VARCHAR(150), country VARCHAR(2), loyalty_tier VARCHAR(20), lifetime_spend NUMERIC(12,2), first_order_at TIMESTAMPTZ, is_active BOOL)
- orders (order_id UUID PRIMARY KEY, customer_id UUID, status VARCHAR(20), gross_total NUMERIC(12,2), discount_amount NUMERIC(10,2), net_... | Using MySQL 8.0, develop a schema-aware query to isolate all 'PREMIUM' tier customers in 'AU' whose 286-day repeat purchase velocity exceeds 16699 USD, and list their total net spent. | <thought>
1. Tables: `customers`, `orders`.
2. Filters: `country = 'AU'`, `loyalty_tier = 'PREMIUM'`, `orders.status IN ('DELIVERED', 'COMPLETED')`.
3. Date Filter (MySQL 8.0): `o.placed_at >= DATE_SUB(NOW(), INTERVAL 286 DAY)`.
4. Group by customer, calculate total net spend and order count.
</thought> | <thought>
1. Tables: `customers`, `orders`.
2. Filters: `country = 'AU'`, `loyalty_tier = 'PREMIUM'`, `orders.status IN ('DELIVERED', 'COMPLETED')`.
3. Date Filter (MySQL 8.0): `o.placed_at >= DATE_SUB(NOW(), INTERVAL 286 DAY)`.
4. Group by customer, calculate total net spend and order count.
</thought>
```sql
WITH cu... | ```sql
SELECT c.customer_id, SUM(o.gross_total)
FROM customers c
JOIN orders o ON c.customer_id = o.customer_id
WHERE c.country = 'AU'
GROUP BY c.customer_id;
``` | WITH customer_spends AS (
SELECT
c.customer_id,
c.email,
COUNT(DISTINCT o.order_id) AS total_orders,
SUM(o.net_total) AS total_net_spent
FROM customers c
INNER JOIN orders o ON c.customer_id = o.customer_id
WHERE c.country = 'AU'
AND c.loyalty_tier = 'PREMIUM'
... | Chosen filters by delivered orders and computes net revenue within native MySQL 8.0 timeframe (o.placed_at >= DATE_SUB(NOW(), INTERVAL 286 DAY)); Rejected sums gross revenue without status or date constraints. |
sql_sft_00019 | Enterprise SQL & Text-to-DB | Healthcare & Hospital Management | MySQL 8.0 | Advanced Relational Window & CTE Synthesis | High | - patients (patient_id UUID PRIMARY KEY, mrn VARCHAR(20), birth_date DATE, gender VARCHAR(10), blood_type VARCHAR(5), primary_physician_id UUID, created_at TIMESTAMPTZ)
- clinical_encounters (encounter_id UUID PRIMARY KEY, patient_id UUID, department VARCHAR(50), admission_time TIMESTAMPTZ, discharge_time TIMESTAMPTZ, ... | In MySQL 8.0, compose an optimized query returning the 49-day all-cause readmission rate and median length of stay in days for the 'PULMONOLOGY' department across all inpatient encounters in 2025. | <thought>
1. Tables: `clinical_encounters`.
2. Department filter: `department = 'PULMONOLOGY'`.
3. Length of stay: `EXTRACT(EPOCH FROM (discharge_time - admission_time)) / 86400.0`.
4. Readmission: `LEAD(admission_time) OVER (PARTITION BY patient_id ORDER BY admission_time)`.
5. Median percentile in MySQL 8.0: `PERCENT... | <thought>
1. Tables: `clinical_encounters`.
2. Department filter: `department = 'PULMONOLOGY'`.
3. Length of stay: `EXTRACT(EPOCH FROM (discharge_time - admission_time)) / 86400.0`.
4. Readmission: `LEAD(admission_time) OVER (PARTITION BY patient_id ORDER BY admission_time)`.
5. Median percentile in MySQL 8.0: `PERCENT... | ```sql
SELECT department, COUNT(encounter_id), AVG(discharge_time - admission_time)
FROM clinical_encounters WHERE department = 'PULMONOLOGY' GROUP BY department;
``` | WITH encounter_timelines AS (
SELECT
encounter_id,
patient_id,
admission_time,
discharge_time,
EXTRACT(EPOCH FROM (discharge_time - admission_time)) / 86400.0 AS los_days,
LEAD(admission_time) OVER (
PARTITION BY patient_id
ORDER BY admission... | Chosen accurately tracks patient readmissions via LEAD() within a 49-day window and uses native MySQL 8.0 median calculation (PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY los_days)); Rejected provides unadjusted averages without readmission tracking. |
sql_sft_00020 | Enterprise SQL & Text-to-DB | Logistics & Global Supply Chain | MySQL 8.0 | Advanced Relational Window & CTE Synthesis | High | - shipments (shipment_id UUID PRIMARY KEY, origin_hub_id UUID, dest_hub_id UUID, carrier_id UUID, status VARCHAR(25), weight_kg NUMERIC(10,2), scheduled_delivery TIMESTAMPTZ, actual_delivery TIMESTAMPTZ)
- tracking_milestones (milestone_id UUID PRIMARY KEY, shipment_id UUID, checkpoint_name VARCHAR(100), latitude NUMER... | Using MySQL 8.0, write an index-optimized query to retrieve logistics carriers handling shipments over 2253 kg whose actual On-Time Delivery (OTD) percentage fell below the contractual SLA target of 91.2% over the last 65 days. | <thought>
1. Tables: `shipments`, `carrier_sla_benchmarks`.
2. Filter: `weight_kg >= 2253`, status = 'DELIVERED'.
3. Time filter (MySQL 8.0): `s.scheduled_delivery >= DATE_SUB(NOW(), INTERVAL 65 DAY)`.
4. OTD: `actual_delivery <= scheduled_delivery`.
5. Group by carrier and calculate adherence percentage.
</thought> | <thought>
1. Tables: `shipments`, `carrier_sla_benchmarks`.
2. Filter: `weight_kg >= 2253`, status = 'DELIVERED'.
3. Time filter (MySQL 8.0): `s.scheduled_delivery >= DATE_SUB(NOW(), INTERVAL 65 DAY)`.
4. OTD: `actual_delivery <= scheduled_delivery`.
5. Group by carrier and calculate adherence percentage.
</thought>
`... | ```sql
SELECT carrier_id, AVG(actual_delivery - scheduled_delivery)
FROM shipments WHERE weight_kg >= 2253 GROUP BY carrier_id;
``` | WITH carrier_perf AS (
SELECT
s.carrier_id,
COUNT(s.shipment_id) AS total_shipments,
SUM(CASE WHEN s.actual_delivery <= s.scheduled_delivery THEN 1 ELSE 0 END) AS on_time_count,
ROUND((SUM(CASE WHEN s.actual_delivery <= s.scheduled_delivery THEN 1.0 ELSE 0.0 END) /
N... | Chosen calculates exact OTD compliance percentage against SLA benchmarks with native MySQL 8.0 date filter (s.scheduled_delivery >= DATE_SUB(NOW(), INTERVAL 65 DAY)) and NULLIF protection; Rejected uses raw timestamp differences without SLA threshold comparison. |
sql_sft_00021 | Enterprise SQL & Text-to-DB | Fintech & Core Banking | Google BigQuery | Advanced Relational Window & CTE Synthesis | High | - accounts (account_id UUID PRIMARY KEY, user_id UUID, currency VARCHAR(3), balance NUMERIC(18,2), account_type VARCHAR(20), status VARCHAR(15), opened_at TIMESTAMPTZ)
- transactions (txn_id UUID PRIMARY KEY, source_acc_id UUID, dest_acc_id UUID, amount NUMERIC(18,2), fee NUMERIC(10,2), status VARCHAR(20), txn_type VAR... | In Google BigQuery, write a window-partitioned query to determine the top 5 largest completed transactions for each active 'INVESTMENT' account over the last 19 days. Include transaction rank, account currency, amount, and timestamp. | <thought>
1. Identify target tables: `accounts` (filtering `status = 'ACTIVE'`, `account_type = 'INVESTMENT'`), and `transactions`.
2. Time window: Native Google BigQuery syntax for 19 days: `t.created_at >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 19 DAY)`.
3. Status filter: Ensure `transactions.status = 'COMPLETED... | <thought>
1. Identify target tables: `accounts` (filtering `status = 'ACTIVE'`, `account_type = 'INVESTMENT'`), and `transactions`.
2. Time window: Native Google BigQuery syntax for 19 days: `t.created_at >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 19 DAY)`.
3. Status filter: Ensure `transactions.status = 'COMPLETED... | ```sql
SELECT a.account_id, t.txn_id, t.amount
FROM accounts a, transactions t
WHERE a.account_id = t.source_acc_id AND a.account_type = 'INVESTMENT'
ORDER BY t.amount DESC LIMIT 5;
``` | WITH ranked_txns AS (
SELECT
a.account_id, a.user_id, a.currency, t.txn_id, t.amount, t.created_at,
DENSE_RANK() OVER (PARTITION BY a.account_id ORDER BY t.amount DESC, t.created_at DESC) AS txn_rank
FROM accounts a
INNER JOIN transactions t ON a.account_id = t.source_acc_id
WHERE a.sta... | Chosen uses native Google BigQuery window partitioning to get top 5 per individual account; Rejected applies an unpartitioned global LIMIT across all accounts combined. |
sql_sft_00022 | Enterprise SQL & Text-to-DB | SaaS & Subscription Analytics | Google BigQuery | Advanced Relational Window & CTE Synthesis | High | - workspaces (workspace_id UUID PRIMARY KEY, owner_id UUID, company_name VARCHAR(100), plan_tier VARCHAR(20), mrr NUMERIC(10,2), seats_allocated INT, created_at TIMESTAMPTZ)
- subscription_invoices (invoice_id UUID PRIMARY KEY, workspace_id UUID, amount_billed NUMERIC(10,2), payment_status VARCHAR(20), billing_period_s... | In Google BigQuery, write a window-partitioned query to determine 'PRO' workspaces with at least 180 allocated seats where average user event activity has dropped by more than 50% over the last 198 days compared to prior period. | <thought>
1. Tables: `workspaces`, `user_activity_events`.
2. Filter: `workspaces.plan_tier = 'PRO'` and `seats_allocated >= 180`.
3. Compare two time windows: `e.created_at >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 198 DAY)` vs prior period.
</thought> | <thought>
1. Tables: `workspaces`, `user_activity_events`.
2. Filter: `workspaces.plan_tier = 'PRO'` and `seats_allocated >= 180`.
3. Compare two time windows: `e.created_at >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 198 DAY)` vs prior period.
</thought>
```sql
WITH activity_summary AS (
SELECT
w.work... | ```sql
SELECT w.workspace_id, COUNT(e.event_id)
FROM workspaces w, user_activity_events e
WHERE w.workspace_id = e.workspace_id
GROUP BY w.workspace_id;
``` | WITH activity_summary AS (
SELECT
w.workspace_id,
w.company_name,
w.seats_allocated,
w.mrr,
COUNT(CASE WHEN e.created_at >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 198 DAY) THEN 1 END) AS current_events,
COUNT(CASE WHEN e.created_at >= TIMESTAMP_SUB(CURRENT_TIMES... | Chosen uses native Google BigQuery conditional aggregation to compare historical time periods with NULLIF division protection; Rejected does an unconstrained join without date windows. |
sql_sft_00023 | Enterprise SQL & Text-to-DB | E-Commerce & Global Retail | Google BigQuery | Advanced Relational Window & CTE Synthesis | High | - customers (customer_id UUID PRIMARY KEY, email VARCHAR(150), country VARCHAR(2), loyalty_tier VARCHAR(20), lifetime_spend NUMERIC(12,2), first_order_at TIMESTAMPTZ, is_active BOOL)
- orders (order_id UUID PRIMARY KEY, customer_id UUID, status VARCHAR(20), gross_total NUMERIC(12,2), discount_amount NUMERIC(10,2), net_... | Using Google BigQuery, compose an optimized query returning all 'TIER_1' tier customers in 'IT' whose 245-day repeat purchase velocity exceeds 33756 USD, and list their total net spent. | <thought>
1. Tables: `customers`, `orders`.
2. Filters: `country = 'IT'`, `loyalty_tier = 'TIER_1'`, `orders.status IN ('DELIVERED', 'COMPLETED')`.
3. Date Filter (Google BigQuery): `o.placed_at >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 245 DAY)`.
4. Group by customer, calculate total net spend and order count.
</... | <thought>
1. Tables: `customers`, `orders`.
2. Filters: `country = 'IT'`, `loyalty_tier = 'TIER_1'`, `orders.status IN ('DELIVERED', 'COMPLETED')`.
3. Date Filter (Google BigQuery): `o.placed_at >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 245 DAY)`.
4. Group by customer, calculate total net spend and order count.
</... | ```sql
SELECT c.customer_id, SUM(o.gross_total)
FROM customers c
JOIN orders o ON c.customer_id = o.customer_id
WHERE c.country = 'IT'
GROUP BY c.customer_id;
``` | WITH customer_spends AS (
SELECT
c.customer_id,
c.email,
COUNT(DISTINCT o.order_id) AS total_orders,
SUM(o.net_total) AS total_net_spent
FROM customers c
INNER JOIN orders o ON c.customer_id = o.customer_id
WHERE c.country = 'IT'
AND c.loyalty_tier = 'TIER_1'
... | Chosen filters by delivered orders and computes net revenue within native Google BigQuery timeframe (o.placed_at >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 245 DAY)); Rejected sums gross revenue without status or date constraints. |
sql_sft_00024 | Enterprise SQL & Text-to-DB | Healthcare & Hospital Management | Google BigQuery | Advanced Relational Window & CTE Synthesis | High | - patients (patient_id UUID PRIMARY KEY, mrn VARCHAR(20), birth_date DATE, gender VARCHAR(10), blood_type VARCHAR(5), primary_physician_id UUID, created_at TIMESTAMPTZ)
- clinical_encounters (encounter_id UUID PRIMARY KEY, patient_id UUID, department VARCHAR(50), admission_time TIMESTAMPTZ, discharge_time TIMESTAMPTZ, ... | In Google BigQuery, develop a schema-aware query to isolate the 49-day all-cause readmission rate and median length of stay in days for the 'CARDIOLOGY' department across all inpatient encounters in 2025. | <thought>
1. Tables: `clinical_encounters`.
2. Department filter: `department = 'CARDIOLOGY'`.
3. Length of stay: `EXTRACT(EPOCH FROM (discharge_time - admission_time)) / 86400.0`.
4. Readmission: `LEAD(admission_time) OVER (PARTITION BY patient_id ORDER BY admission_time)`.
5. Median percentile in Google BigQuery: `PE... | <thought>
1. Tables: `clinical_encounters`.
2. Department filter: `department = 'CARDIOLOGY'`.
3. Length of stay: `EXTRACT(EPOCH FROM (discharge_time - admission_time)) / 86400.0`.
4. Readmission: `LEAD(admission_time) OVER (PARTITION BY patient_id ORDER BY admission_time)`.
5. Median percentile in Google BigQuery: `PE... | ```sql
SELECT department, COUNT(encounter_id), AVG(discharge_time - admission_time)
FROM clinical_encounters WHERE department = 'CARDIOLOGY' GROUP BY department;
``` | WITH encounter_timelines AS (
SELECT
encounter_id,
patient_id,
admission_time,
discharge_time,
EXTRACT(EPOCH FROM (discharge_time - admission_time)) / 86400.0 AS los_days,
LEAD(admission_time) OVER (
PARTITION BY patient_id
ORDER BY admission... | Chosen accurately tracks patient readmissions via LEAD() within a 49-day window and uses native Google BigQuery median calculation (PERCENTILE_CONT(los_days, 0.5) OVER()); Rejected provides unadjusted averages without readmission tracking. |
sql_sft_00025 | Enterprise SQL & Text-to-DB | Logistics & Global Supply Chain | Google BigQuery | Advanced Relational Window & CTE Synthesis | High | - shipments (shipment_id UUID PRIMARY KEY, origin_hub_id UUID, dest_hub_id UUID, carrier_id UUID, status VARCHAR(25), weight_kg NUMERIC(10,2), scheduled_delivery TIMESTAMPTZ, actual_delivery TIMESTAMPTZ)
- tracking_milestones (milestone_id UUID PRIMARY KEY, shipment_id UUID, checkpoint_name VARCHAR(100), latitude NUMER... | Using Google BigQuery, write a window-partitioned query to determine logistics carriers handling shipments over 855 kg whose actual On-Time Delivery (OTD) percentage fell below the contractual SLA target of 98.1% over the last 131 days. | <thought>
1. Tables: `shipments`, `carrier_sla_benchmarks`.
2. Filter: `weight_kg >= 855`, status = 'DELIVERED'.
3. Time filter (Google BigQuery): `s.scheduled_delivery >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 131 DAY)`.
4. OTD: `actual_delivery <= scheduled_delivery`.
5. Group by carrier and calculate adherence ... | <thought>
1. Tables: `shipments`, `carrier_sla_benchmarks`.
2. Filter: `weight_kg >= 855`, status = 'DELIVERED'.
3. Time filter (Google BigQuery): `s.scheduled_delivery >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 131 DAY)`.
4. OTD: `actual_delivery <= scheduled_delivery`.
5. Group by carrier and calculate adherence ... | ```sql
SELECT carrier_id, AVG(actual_delivery - scheduled_delivery)
FROM shipments WHERE weight_kg >= 855 GROUP BY carrier_id;
``` | WITH carrier_perf AS (
SELECT
s.carrier_id,
COUNT(s.shipment_id) AS total_shipments,
SUM(CASE WHEN s.actual_delivery <= s.scheduled_delivery THEN 1 ELSE 0 END) AS on_time_count,
ROUND((SUM(CASE WHEN s.actual_delivery <= s.scheduled_delivery THEN 1.0 ELSE 0.0 END) /
N... | Chosen calculates exact OTD compliance percentage against SLA benchmarks with native Google BigQuery date filter (s.scheduled_delivery >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 131 DAY)) and NULLIF protection; Rejected uses raw timestamp differences without SLA threshold comparison. |
sql_sft_00026 | Enterprise SQL & Text-to-DB | Fintech & Core Banking | PostgreSQL 16 | Advanced Relational Window & CTE Synthesis | High | - accounts (account_id UUID PRIMARY KEY, user_id UUID, currency VARCHAR(3), balance NUMERIC(18,2), account_type VARCHAR(20), status VARCHAR(15), opened_at TIMESTAMPTZ)
- transactions (txn_id UUID PRIMARY KEY, source_acc_id UUID, dest_acc_id UUID, amount NUMERIC(18,2), fee NUMERIC(10,2), status VARCHAR(20), txn_type VAR... | Using PostgreSQL 16, compose an optimized query returning the daily net transaction inflow for currency 'JPY' over the last 355 days. Filter out any transactions flagged with an AML risk score exceeding 94. | <thought>
1. Table selection: `transactions` with left joins to `accounts` for currency verification.
2. Inflow vs Outflow: Inflow is incoming (dest_acc_id), Outflow is outgoing (source_acc_id).
3. AML Exclusion: Use `NOT EXISTS` against `aml_screenings` with `risk_score > 94` to avoid dropping unscreened transactions.... | <thought>
1. Table selection: `transactions` with left joins to `accounts` for currency verification.
2. Inflow vs Outflow: Inflow is incoming (dest_acc_id), Outflow is outgoing (source_acc_id).
3. AML Exclusion: Use `NOT EXISTS` against `aml_screenings` with `risk_score > 94` to avoid dropping unscreened transactions.... | ```sql
SELECT CAST(t.created_at AS DATE), SUM(t.amount)
FROM transactions t
INNER JOIN aml_screenings s ON t.txn_id = s.txn_id
WHERE s.risk_score <= 94
GROUP BY 1;
``` | SELECT
CAST(t.created_at AS DATE) AS txn_date,
SUM(CASE WHEN a_dest.currency = 'JPY' THEN t.amount ELSE 0 END) AS total_inflow,
SUM(CASE WHEN a_src.currency = 'JPY' THEN t.amount ELSE 0 END) AS total_outflow,
SUM(CASE WHEN a_dest.currency = 'JPY' THEN t.amount ELSE 0 END) -
SUM(CASE WHEN a_src.cur... | Chosen uses NOT EXISTS to keep clean unscreened transactions and separates inflow from outflow; Rejected uses INNER JOIN which drops all clean transactions that haven't been manually screened. |
sql_sft_00027 | Enterprise SQL & Text-to-DB | SaaS & Subscription Analytics | PostgreSQL 16 | Advanced Relational Window & CTE Synthesis | High | - workspaces (workspace_id UUID PRIMARY KEY, owner_id UUID, company_name VARCHAR(100), plan_tier VARCHAR(20), mrr NUMERIC(10,2), seats_allocated INT, created_at TIMESTAMPTZ)
- subscription_invoices (invoice_id UUID PRIMARY KEY, workspace_id UUID, amount_billed NUMERIC(10,2), payment_status VARCHAR(20), billing_period_s... | Using PostgreSQL 16, write a window-partitioned query to determine the Month-over-Month MRR expansion and net contraction for 'STARTER' workspaces over the last 259 days, using paid invoices. | <thought>
1. Tables: `workspaces` (filtering `plan_tier = 'STARTER'`), `subscription_invoices` (`payment_status = 'PAID'`).
2. Monthly revenue aggregation in PostgreSQL 16: `DATE_TRUNC('month', i.billing_period_start)`.
3. Time filter in PostgreSQL 16: `i.billing_period_start >= CURRENT_TIMESTAMP - INTERVAL '259 days'`... | <thought>
1. Tables: `workspaces` (filtering `plan_tier = 'STARTER'`), `subscription_invoices` (`payment_status = 'PAID'`).
2. Monthly revenue aggregation in PostgreSQL 16: `DATE_TRUNC('month', i.billing_period_start)`.
3. Time filter in PostgreSQL 16: `i.billing_period_start >= CURRENT_TIMESTAMP - INTERVAL '259 days'`... | ```sql
SELECT DATE_TRUNC('month', i.billing_period_start), SUM(amount_billed)
FROM subscription_invoices WHERE payment_status = 'PAID' GROUP BY 1;
``` | WITH monthly_workspace_rev AS (
SELECT
w.workspace_id,
w.company_name,
DATE_TRUNC('month', i.billing_period_start) AS billing_month,
SUM(i.amount_billed) AS current_mrr,
LAG(SUM(i.amount_billed)) OVER (
PARTITION BY w.workspace_id
ORDER BY DATE_TRUNC... | Chosen leverages native PostgreSQL 16 date truncation (DATE_TRUNC('month', i.billing_period_start)) and LAG() over cohorts; Rejected merges all revenue without cohort delta tracking. |
sql_sft_00028 | Enterprise SQL & Text-to-DB | E-Commerce & Global Retail | PostgreSQL 16 | Advanced Relational Window & CTE Synthesis | High | - customers (customer_id UUID PRIMARY KEY, email VARCHAR(150), country VARCHAR(2), loyalty_tier VARCHAR(20), lifetime_spend NUMERIC(12,2), first_order_at TIMESTAMPTZ, is_active BOOL)
- orders (order_id UUID PRIMARY KEY, customer_id UUID, status VARCHAR(20), gross_total NUMERIC(12,2), discount_amount NUMERIC(10,2), net_... | Using PostgreSQL 16, generate a production-ready analytical query to compute all 'GOLD' tier customers in 'AU' whose 196-day repeat purchase velocity exceeds 6584 USD, and list their total net spent. | <thought>
1. Tables: `customers`, `orders`.
2. Filters: `country = 'AU'`, `loyalty_tier = 'GOLD'`, `orders.status IN ('DELIVERED', 'COMPLETED')`.
3. Date Filter (PostgreSQL 16): `o.placed_at >= CURRENT_TIMESTAMP - INTERVAL '196 days'`.
4. Group by customer, calculate total net spend and order count.
</thought> | <thought>
1. Tables: `customers`, `orders`.
2. Filters: `country = 'AU'`, `loyalty_tier = 'GOLD'`, `orders.status IN ('DELIVERED', 'COMPLETED')`.
3. Date Filter (PostgreSQL 16): `o.placed_at >= CURRENT_TIMESTAMP - INTERVAL '196 days'`.
4. Group by customer, calculate total net spend and order count.
</thought>
```sql
... | ```sql
SELECT c.customer_id, SUM(o.gross_total)
FROM customers c
JOIN orders o ON c.customer_id = o.customer_id
WHERE c.country = 'AU'
GROUP BY c.customer_id;
``` | WITH customer_spends AS (
SELECT
c.customer_id,
c.email,
COUNT(DISTINCT o.order_id) AS total_orders,
SUM(o.net_total) AS total_net_spent
FROM customers c
INNER JOIN orders o ON c.customer_id = o.customer_id
WHERE c.country = 'AU'
AND c.loyalty_tier = 'GOLD'
A... | Chosen filters by delivered orders and computes net revenue within native PostgreSQL 16 timeframe (o.placed_at >= CURRENT_TIMESTAMP - INTERVAL '196 days'); Rejected sums gross revenue without status or date constraints. |
sql_sft_00029 | Enterprise SQL & Text-to-DB | Healthcare & Hospital Management | PostgreSQL 16 | Advanced Relational Window & CTE Synthesis | High | - patients (patient_id UUID PRIMARY KEY, mrn VARCHAR(20), birth_date DATE, gender VARCHAR(10), blood_type VARCHAR(5), primary_physician_id UUID, created_at TIMESTAMPTZ)
- clinical_encounters (encounter_id UUID PRIMARY KEY, patient_id UUID, department VARCHAR(50), admission_time TIMESTAMPTZ, discharge_time TIMESTAMPTZ, ... | In PostgreSQL 16, construct a high-performance SQL query to find the 42-day all-cause readmission rate and median length of stay in days for the 'CARDIOLOGY' department across all inpatient encounters in 2024. | <thought>
1. Tables: `clinical_encounters`.
2. Department filter: `department = 'CARDIOLOGY'`.
3. Length of stay: `EXTRACT(EPOCH FROM (discharge_time - admission_time)) / 86400.0`.
4. Readmission: `LEAD(admission_time) OVER (PARTITION BY patient_id ORDER BY admission_time)`.
5. Median percentile in PostgreSQL 16: `PERC... | <thought>
1. Tables: `clinical_encounters`.
2. Department filter: `department = 'CARDIOLOGY'`.
3. Length of stay: `EXTRACT(EPOCH FROM (discharge_time - admission_time)) / 86400.0`.
4. Readmission: `LEAD(admission_time) OVER (PARTITION BY patient_id ORDER BY admission_time)`.
5. Median percentile in PostgreSQL 16: `PERC... | ```sql
SELECT department, COUNT(encounter_id), AVG(discharge_time - admission_time)
FROM clinical_encounters WHERE department = 'CARDIOLOGY' GROUP BY department;
``` | WITH encounter_timelines AS (
SELECT
encounter_id,
patient_id,
admission_time,
discharge_time,
EXTRACT(EPOCH FROM (discharge_time - admission_time)) / 86400.0 AS los_days,
LEAD(admission_time) OVER (
PARTITION BY patient_id
ORDER BY admission... | Chosen accurately tracks patient readmissions via LEAD() within a 42-day window and uses native PostgreSQL 16 median calculation (PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY los_days)); Rejected provides unadjusted averages without readmission tracking. |
sql_sft_00030 | Enterprise SQL & Text-to-DB | Logistics & Global Supply Chain | PostgreSQL 16 | Advanced Relational Window & CTE Synthesis | High | - shipments (shipment_id UUID PRIMARY KEY, origin_hub_id UUID, dest_hub_id UUID, carrier_id UUID, status VARCHAR(25), weight_kg NUMERIC(10,2), scheduled_delivery TIMESTAMPTZ, actual_delivery TIMESTAMPTZ)
- tracking_milestones (milestone_id UUID PRIMARY KEY, shipment_id UUID, checkpoint_name VARCHAR(100), latitude NUMER... | Using PostgreSQL 16, write a CTE-based SQL query to extract logistics carriers handling shipments over 730 kg whose actual On-Time Delivery (OTD) percentage fell below the contractual SLA target of 94.4% over the last 129 days. | <thought>
1. Tables: `shipments`, `carrier_sla_benchmarks`.
2. Filter: `weight_kg >= 730`, status = 'DELIVERED'.
3. Time filter (PostgreSQL 16): `s.scheduled_delivery >= CURRENT_TIMESTAMP - INTERVAL '129 days'`.
4. OTD: `actual_delivery <= scheduled_delivery`.
5. Group by carrier and calculate adherence percentage.
</t... | <thought>
1. Tables: `shipments`, `carrier_sla_benchmarks`.
2. Filter: `weight_kg >= 730`, status = 'DELIVERED'.
3. Time filter (PostgreSQL 16): `s.scheduled_delivery >= CURRENT_TIMESTAMP - INTERVAL '129 days'`.
4. OTD: `actual_delivery <= scheduled_delivery`.
5. Group by carrier and calculate adherence percentage.
</t... | ```sql
SELECT carrier_id, AVG(actual_delivery - scheduled_delivery)
FROM shipments WHERE weight_kg >= 730 GROUP BY carrier_id;
``` | WITH carrier_perf AS (
SELECT
s.carrier_id,
COUNT(s.shipment_id) AS total_shipments,
SUM(CASE WHEN s.actual_delivery <= s.scheduled_delivery THEN 1 ELSE 0 END) AS on_time_count,
ROUND((SUM(CASE WHEN s.actual_delivery <= s.scheduled_delivery THEN 1.0 ELSE 0.0 END) /
N... | Chosen calculates exact OTD compliance percentage against SLA benchmarks with native PostgreSQL 16 date filter (s.scheduled_delivery >= CURRENT_TIMESTAMP - INTERVAL '129 days') and NULLIF protection; Rejected uses raw timestamp differences without SLA threshold comparison. |
sql_sft_00031 | Enterprise SQL & Text-to-DB | Fintech & Core Banking | Snowflake | Advanced Relational Window & CTE Synthesis | High | - accounts (account_id UUID PRIMARY KEY, user_id UUID, currency VARCHAR(3), balance NUMERIC(18,2), account_type VARCHAR(20), status VARCHAR(15), opened_at TIMESTAMPTZ)
- transactions (txn_id UUID PRIMARY KEY, source_acc_id UUID, dest_acc_id UUID, amount NUMERIC(18,2), fee NUMERIC(10,2), status VARCHAR(20), txn_type VAR... | In Snowflake, compute the 7-day rolling moving average balance and cumulative volume for accounts exceeding a closing balance of 6353 NZD. | <thought>
1. Table: `daily_balance_snapshots` joined with `accounts`.
2. Window: `AVG(closing_balance) OVER (PARTITION BY account_id ORDER BY snapshot_date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW)`.
3. Filter by currency 'NZD' and closing balance >= 6353.
</thought> | <thought>
1. Table: `daily_balance_snapshots` joined with `accounts`.
2. Window: `AVG(closing_balance) OVER (PARTITION BY account_id ORDER BY snapshot_date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW)`.
3. Filter by currency 'NZD' and closing balance >= 6353.
</thought>
```sql
SELECT
s.account_id,
s.snapshot_dat... | ```sql
SELECT account_id, AVG(closing_balance)
FROM daily_balance_snapshots
GROUP BY account_id;
``` | SELECT
s.account_id,
s.snapshot_date,
s.closing_balance,
AVG(s.closing_balance) OVER (
PARTITION BY s.account_id
ORDER BY s.snapshot_date
ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
) AS rolling_7d_avg_balance,
SUM(s.total_inflow + s.total_outflow) OVER (
PARTITIO... | Chosen uses a sliding window frame (ROWS BETWEEN 6 PRECEDING AND CURRENT ROW); Rejected collapses all time series into a single static scalar average. |
sql_sft_00032 | Enterprise SQL & Text-to-DB | SaaS & Subscription Analytics | Snowflake | Advanced Relational Window & CTE Synthesis | High | - workspaces (workspace_id UUID PRIMARY KEY, owner_id UUID, company_name VARCHAR(100), plan_tier VARCHAR(20), mrr NUMERIC(10,2), seats_allocated INT, created_at TIMESTAMPTZ)
- subscription_invoices (invoice_id UUID PRIMARY KEY, workspace_id UUID, amount_billed NUMERIC(10,2), payment_status VARCHAR(20), billing_period_s... | In Snowflake, generate a production-ready analytical query to compute 'UNLIMITED' workspaces with at least 94 allocated seats where average user event activity has dropped by more than 50% over the last 356 days compared to prior period. | <thought>
1. Tables: `workspaces`, `user_activity_events`.
2. Filter: `workspaces.plan_tier = 'UNLIMITED'` and `seats_allocated >= 94`.
3. Compare two time windows: `e.created_at >= DATEADD(day, -356, CURRENT_TIMESTAMP())` vs prior period.
</thought> | <thought>
1. Tables: `workspaces`, `user_activity_events`.
2. Filter: `workspaces.plan_tier = 'UNLIMITED'` and `seats_allocated >= 94`.
3. Compare two time windows: `e.created_at >= DATEADD(day, -356, CURRENT_TIMESTAMP())` vs prior period.
</thought>
```sql
WITH activity_summary AS (
SELECT
w.workspace_id... | ```sql
SELECT w.workspace_id, COUNT(e.event_id)
FROM workspaces w, user_activity_events e
WHERE w.workspace_id = e.workspace_id
GROUP BY w.workspace_id;
``` | WITH activity_summary AS (
SELECT
w.workspace_id,
w.company_name,
w.seats_allocated,
w.mrr,
COUNT(CASE WHEN e.created_at >= DATEADD(day, -356, CURRENT_TIMESTAMP()) THEN 1 END) AS current_events,
COUNT(CASE WHEN e.created_at >= DATEADD(day, -712, CURRENT_TIMESTAMP()) ... | Chosen uses native Snowflake conditional aggregation to compare historical time periods with NULLIF division protection; Rejected does an unconstrained join without date windows. |
sql_sft_00033 | Enterprise SQL & Text-to-DB | E-Commerce & Global Retail | Snowflake | Advanced Relational Window & CTE Synthesis | High | - customers (customer_id UUID PRIMARY KEY, email VARCHAR(150), country VARCHAR(2), loyalty_tier VARCHAR(20), lifetime_spend NUMERIC(12,2), first_order_at TIMESTAMPTZ, is_active BOOL)
- orders (order_id UUID PRIMARY KEY, customer_id UUID, status VARCHAR(20), gross_total NUMERIC(12,2), discount_amount NUMERIC(10,2), net_... | Using Snowflake, write a window-partitioned query to determine all 'DIAMOND' tier customers in 'NL' whose 9-day repeat purchase velocity exceeds 20791 USD, and list their total net spent. | <thought>
1. Tables: `customers`, `orders`.
2. Filters: `country = 'NL'`, `loyalty_tier = 'DIAMOND'`, `orders.status IN ('DELIVERED', 'COMPLETED')`.
3. Date Filter (Snowflake): `o.placed_at >= DATEADD(day, -9, CURRENT_TIMESTAMP())`.
4. Group by customer, calculate total net spend and order count.
</thought> | <thought>
1. Tables: `customers`, `orders`.
2. Filters: `country = 'NL'`, `loyalty_tier = 'DIAMOND'`, `orders.status IN ('DELIVERED', 'COMPLETED')`.
3. Date Filter (Snowflake): `o.placed_at >= DATEADD(day, -9, CURRENT_TIMESTAMP())`.
4. Group by customer, calculate total net spend and order count.
</thought>
```sql
WIT... | ```sql
SELECT c.customer_id, SUM(o.gross_total)
FROM customers c
JOIN orders o ON c.customer_id = o.customer_id
WHERE c.country = 'NL'
GROUP BY c.customer_id;
``` | WITH customer_spends AS (
SELECT
c.customer_id,
c.email,
COUNT(DISTINCT o.order_id) AS total_orders,
SUM(o.net_total) AS total_net_spent
FROM customers c
INNER JOIN orders o ON c.customer_id = o.customer_id
WHERE c.country = 'NL'
AND c.loyalty_tier = 'DIAMOND'
... | Chosen filters by delivered orders and computes net revenue within native Snowflake timeframe (o.placed_at >= DATEADD(day, -9, CURRENT_TIMESTAMP())); Rejected sums gross revenue without status or date constraints. |
sql_sft_00034 | Enterprise SQL & Text-to-DB | Healthcare & Hospital Management | Snowflake | Advanced Relational Window & CTE Synthesis | High | - patients (patient_id UUID PRIMARY KEY, mrn VARCHAR(20), birth_date DATE, gender VARCHAR(10), blood_type VARCHAR(5), primary_physician_id UUID, created_at TIMESTAMPTZ)
- clinical_encounters (encounter_id UUID PRIMARY KEY, patient_id UUID, department VARCHAR(50), admission_time TIMESTAMPTZ, discharge_time TIMESTAMPTZ, ... | In Snowflake, write a CTE-based SQL query to extract the 58-day all-cause readmission rate and median length of stay in days for the 'NEUROLOGY' department across all inpatient encounters in 2025. | <thought>
1. Tables: `clinical_encounters`.
2. Department filter: `department = 'NEUROLOGY'`.
3. Length of stay: `EXTRACT(EPOCH FROM (discharge_time - admission_time)) / 86400.0`.
4. Readmission: `LEAD(admission_time) OVER (PARTITION BY patient_id ORDER BY admission_time)`.
5. Median percentile in Snowflake: `PERCENTIL... | <thought>
1. Tables: `clinical_encounters`.
2. Department filter: `department = 'NEUROLOGY'`.
3. Length of stay: `EXTRACT(EPOCH FROM (discharge_time - admission_time)) / 86400.0`.
4. Readmission: `LEAD(admission_time) OVER (PARTITION BY patient_id ORDER BY admission_time)`.
5. Median percentile in Snowflake: `PERCENTIL... | ```sql
SELECT department, COUNT(encounter_id), AVG(discharge_time - admission_time)
FROM clinical_encounters WHERE department = 'NEUROLOGY' GROUP BY department;
``` | WITH encounter_timelines AS (
SELECT
encounter_id,
patient_id,
admission_time,
discharge_time,
EXTRACT(EPOCH FROM (discharge_time - admission_time)) / 86400.0 AS los_days,
LEAD(admission_time) OVER (
PARTITION BY patient_id
ORDER BY admission... | Chosen accurately tracks patient readmissions via LEAD() within a 58-day window and uses native Snowflake median calculation (PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY los_days)); Rejected provides unadjusted averages without readmission tracking. |
sql_sft_00035 | Enterprise SQL & Text-to-DB | Logistics & Global Supply Chain | Snowflake | Advanced Relational Window & CTE Synthesis | High | - shipments (shipment_id UUID PRIMARY KEY, origin_hub_id UUID, dest_hub_id UUID, carrier_id UUID, status VARCHAR(25), weight_kg NUMERIC(10,2), scheduled_delivery TIMESTAMPTZ, actual_delivery TIMESTAMPTZ)
- tracking_milestones (milestone_id UUID PRIMARY KEY, shipment_id UUID, checkpoint_name VARCHAR(100), latitude NUMER... | Using Snowflake, develop a schema-aware query to isolate logistics carriers handling shipments over 4949 kg whose actual On-Time Delivery (OTD) percentage fell below the contractual SLA target of 89.4% over the last 164 days. | <thought>
1. Tables: `shipments`, `carrier_sla_benchmarks`.
2. Filter: `weight_kg >= 4949`, status = 'DELIVERED'.
3. Time filter (Snowflake): `s.scheduled_delivery >= DATEADD(day, -164, CURRENT_TIMESTAMP())`.
4. OTD: `actual_delivery <= scheduled_delivery`.
5. Group by carrier and calculate adherence percentage.
</thou... | <thought>
1. Tables: `shipments`, `carrier_sla_benchmarks`.
2. Filter: `weight_kg >= 4949`, status = 'DELIVERED'.
3. Time filter (Snowflake): `s.scheduled_delivery >= DATEADD(day, -164, CURRENT_TIMESTAMP())`.
4. OTD: `actual_delivery <= scheduled_delivery`.
5. Group by carrier and calculate adherence percentage.
</thou... | ```sql
SELECT carrier_id, AVG(actual_delivery - scheduled_delivery)
FROM shipments WHERE weight_kg >= 4949 GROUP BY carrier_id;
``` | WITH carrier_perf AS (
SELECT
s.carrier_id,
COUNT(s.shipment_id) AS total_shipments,
SUM(CASE WHEN s.actual_delivery <= s.scheduled_delivery THEN 1 ELSE 0 END) AS on_time_count,
ROUND((SUM(CASE WHEN s.actual_delivery <= s.scheduled_delivery THEN 1.0 ELSE 0.0 END) /
N... | Chosen calculates exact OTD compliance percentage against SLA benchmarks with native Snowflake date filter (s.scheduled_delivery >= DATEADD(day, -164, CURRENT_TIMESTAMP())) and NULLIF protection; Rejected uses raw timestamp differences without SLA threshold comparison. |
sql_sft_00036 | Enterprise SQL & Text-to-DB | Fintech & Core Banking | ClickHouse (OLAP) | Advanced Relational Window & CTE Synthesis | High | - accounts (account_id UUID PRIMARY KEY, user_id UUID, currency VARCHAR(3), balance NUMERIC(18,2), account_type VARCHAR(20), status VARCHAR(15), opened_at TIMESTAMPTZ)
- transactions (txn_id UUID PRIMARY KEY, source_acc_id UUID, dest_acc_id UUID, amount NUMERIC(18,2), fee NUMERIC(10,2), status VARCHAR(20), txn_type VAR... | In ClickHouse (OLAP), compute the 7-day rolling moving average balance and cumulative volume for accounts exceeding a closing balance of 18240 GBP. | <thought>
1. Table: `daily_balance_snapshots` joined with `accounts`.
2. Window: `AVG(closing_balance) OVER (PARTITION BY account_id ORDER BY snapshot_date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW)`.
3. Filter by currency 'GBP' and closing balance >= 18240.
</thought> | <thought>
1. Table: `daily_balance_snapshots` joined with `accounts`.
2. Window: `AVG(closing_balance) OVER (PARTITION BY account_id ORDER BY snapshot_date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW)`.
3. Filter by currency 'GBP' and closing balance >= 18240.
</thought>
```sql
SELECT
s.account_id,
s.snapshot_da... | ```sql
SELECT account_id, AVG(closing_balance)
FROM daily_balance_snapshots
GROUP BY account_id;
``` | SELECT
s.account_id,
s.snapshot_date,
s.closing_balance,
AVG(s.closing_balance) OVER (
PARTITION BY s.account_id
ORDER BY s.snapshot_date
ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
) AS rolling_7d_avg_balance,
SUM(s.total_inflow + s.total_outflow) OVER (
PARTITIO... | Chosen uses a sliding window frame (ROWS BETWEEN 6 PRECEDING AND CURRENT ROW); Rejected collapses all time series into a single static scalar average. |
sql_sft_00037 | Enterprise SQL & Text-to-DB | SaaS & Subscription Analytics | ClickHouse (OLAP) | Advanced Relational Window & CTE Synthesis | High | - workspaces (workspace_id UUID PRIMARY KEY, owner_id UUID, company_name VARCHAR(100), plan_tier VARCHAR(20), mrr NUMERIC(10,2), seats_allocated INT, created_at TIMESTAMPTZ)
- subscription_invoices (invoice_id UUID PRIMARY KEY, workspace_id UUID, amount_billed NUMERIC(10,2), payment_status VARCHAR(20), billing_period_s... | Using ClickHouse (OLAP), write a CTE-based SQL query to extract the Month-over-Month MRR expansion and net contraction for 'ENTERPRISE' workspaces over the last 273 days, using paid invoices. | <thought>
1. Tables: `workspaces` (filtering `plan_tier = 'ENTERPRISE'`), `subscription_invoices` (`payment_status = 'PAID'`).
2. Monthly revenue aggregation in ClickHouse (OLAP): `toStartOfMonth(i.billing_period_start)`.
3. Time filter in ClickHouse (OLAP): `i.billing_period_start >= subtractDays(now(), 273)`.
4. Coho... | <thought>
1. Tables: `workspaces` (filtering `plan_tier = 'ENTERPRISE'`), `subscription_invoices` (`payment_status = 'PAID'`).
2. Monthly revenue aggregation in ClickHouse (OLAP): `toStartOfMonth(i.billing_period_start)`.
3. Time filter in ClickHouse (OLAP): `i.billing_period_start >= subtractDays(now(), 273)`.
4. Coho... | ```sql
SELECT toStartOfMonth(i.billing_period_start), SUM(amount_billed)
FROM subscription_invoices WHERE payment_status = 'PAID' GROUP BY 1;
``` | WITH monthly_workspace_rev AS (
SELECT
w.workspace_id,
w.company_name,
toStartOfMonth(i.billing_period_start) AS billing_month,
SUM(i.amount_billed) AS current_mrr,
LAG(SUM(i.amount_billed)) OVER (
PARTITION BY w.workspace_id
ORDER BY toStartOfMonth(... | Chosen leverages native ClickHouse (OLAP) date truncation (toStartOfMonth(i.billing_period_start)) and LAG() over cohorts; Rejected merges all revenue without cohort delta tracking. |
sql_sft_00038 | Enterprise SQL & Text-to-DB | E-Commerce & Global Retail | ClickHouse (OLAP) | Advanced Relational Window & CTE Synthesis | High | - customers (customer_id UUID PRIMARY KEY, email VARCHAR(150), country VARCHAR(2), loyalty_tier VARCHAR(20), lifetime_spend NUMERIC(12,2), first_order_at TIMESTAMPTZ, is_active BOOL)
- orders (order_id UUID PRIMARY KEY, customer_id UUID, status VARCHAR(20), gross_total NUMERIC(12,2), discount_amount NUMERIC(10,2), net_... | Using ClickHouse (OLAP), construct a high-performance SQL query to find all 'PLATINUM' tier customers in 'AU' whose 199-day repeat purchase velocity exceeds 1176 USD, and list their total net spent. | <thought>
1. Tables: `customers`, `orders`.
2. Filters: `country = 'AU'`, `loyalty_tier = 'PLATINUM'`, `orders.status IN ('DELIVERED', 'COMPLETED')`.
3. Date Filter (ClickHouse (OLAP)): `o.placed_at >= subtractDays(now(), 199)`.
4. Group by customer, calculate total net spend and order count.
</thought> | <thought>
1. Tables: `customers`, `orders`.
2. Filters: `country = 'AU'`, `loyalty_tier = 'PLATINUM'`, `orders.status IN ('DELIVERED', 'COMPLETED')`.
3. Date Filter (ClickHouse (OLAP)): `o.placed_at >= subtractDays(now(), 199)`.
4. Group by customer, calculate total net spend and order count.
</thought>
```sql
WITH cu... | ```sql
SELECT c.customer_id, SUM(o.gross_total)
FROM customers c
JOIN orders o ON c.customer_id = o.customer_id
WHERE c.country = 'AU'
GROUP BY c.customer_id;
``` | WITH customer_spends AS (
SELECT
c.customer_id,
c.email,
COUNT(DISTINCT o.order_id) AS total_orders,
SUM(o.net_total) AS total_net_spent
FROM customers c
INNER JOIN orders o ON c.customer_id = o.customer_id
WHERE c.country = 'AU'
AND c.loyalty_tier = 'PLATINUM'
... | Chosen filters by delivered orders and computes net revenue within native ClickHouse (OLAP) timeframe (o.placed_at >= subtractDays(now(), 199)); Rejected sums gross revenue without status or date constraints. |
sql_sft_00039 | Enterprise SQL & Text-to-DB | Healthcare & Hospital Management | ClickHouse (OLAP) | Advanced Relational Window & CTE Synthesis | High | - patients (patient_id UUID PRIMARY KEY, mrn VARCHAR(20), birth_date DATE, gender VARCHAR(10), blood_type VARCHAR(5), primary_physician_id UUID, created_at TIMESTAMPTZ)
- clinical_encounters (encounter_id UUID PRIMARY KEY, patient_id UUID, department VARCHAR(50), admission_time TIMESTAMPTZ, discharge_time TIMESTAMPTZ, ... | In ClickHouse (OLAP), calculate the exact breakdown of the 17-day all-cause readmission rate and median length of stay in days for the 'ICU' department across all inpatient encounters in 2024. | <thought>
1. Tables: `clinical_encounters`.
2. Department filter: `department = 'ICU'`.
3. Length of stay: `EXTRACT(EPOCH FROM (discharge_time - admission_time)) / 86400.0`.
4. Readmission: `LEAD(admission_time) OVER (PARTITION BY patient_id ORDER BY admission_time)`.
5. Median percentile in ClickHouse (OLAP): `quantil... | <thought>
1. Tables: `clinical_encounters`.
2. Department filter: `department = 'ICU'`.
3. Length of stay: `EXTRACT(EPOCH FROM (discharge_time - admission_time)) / 86400.0`.
4. Readmission: `LEAD(admission_time) OVER (PARTITION BY patient_id ORDER BY admission_time)`.
5. Median percentile in ClickHouse (OLAP): `quantil... | ```sql
SELECT department, COUNT(encounter_id), AVG(discharge_time - admission_time)
FROM clinical_encounters WHERE department = 'ICU' GROUP BY department;
``` | WITH encounter_timelines AS (
SELECT
encounter_id,
patient_id,
admission_time,
discharge_time,
EXTRACT(EPOCH FROM (discharge_time - admission_time)) / 86400.0 AS los_days,
LEAD(admission_time) OVER (
PARTITION BY patient_id
ORDER BY admission... | Chosen accurately tracks patient readmissions via LEAD() within a 17-day window and uses native ClickHouse (OLAP) median calculation (quantile(0.5)(los_days)); Rejected provides unadjusted averages without readmission tracking. |
sql_sft_00040 | Enterprise SQL & Text-to-DB | Logistics & Global Supply Chain | ClickHouse (OLAP) | Advanced Relational Window & CTE Synthesis | High | - shipments (shipment_id UUID PRIMARY KEY, origin_hub_id UUID, dest_hub_id UUID, carrier_id UUID, status VARCHAR(25), weight_kg NUMERIC(10,2), scheduled_delivery TIMESTAMPTZ, actual_delivery TIMESTAMPTZ)
- tracking_milestones (milestone_id UUID PRIMARY KEY, shipment_id UUID, checkpoint_name VARCHAR(100), latitude NUMER... | Using ClickHouse (OLAP), develop a schema-aware query to isolate logistics carriers handling shipments over 2735 kg whose actual On-Time Delivery (OTD) percentage fell below the contractual SLA target of 93.6% over the last 20 days. | <thought>
1. Tables: `shipments`, `carrier_sla_benchmarks`.
2. Filter: `weight_kg >= 2735`, status = 'DELIVERED'.
3. Time filter (ClickHouse (OLAP)): `s.scheduled_delivery >= subtractDays(now(), 20)`.
4. OTD: `actual_delivery <= scheduled_delivery`.
5. Group by carrier and calculate adherence percentage.
</thought> | <thought>
1. Tables: `shipments`, `carrier_sla_benchmarks`.
2. Filter: `weight_kg >= 2735`, status = 'DELIVERED'.
3. Time filter (ClickHouse (OLAP)): `s.scheduled_delivery >= subtractDays(now(), 20)`.
4. OTD: `actual_delivery <= scheduled_delivery`.
5. Group by carrier and calculate adherence percentage.
</thought>
``... | ```sql
SELECT carrier_id, AVG(actual_delivery - scheduled_delivery)
FROM shipments WHERE weight_kg >= 2735 GROUP BY carrier_id;
``` | WITH carrier_perf AS (
SELECT
s.carrier_id,
COUNT(s.shipment_id) AS total_shipments,
SUM(CASE WHEN s.actual_delivery <= s.scheduled_delivery THEN 1 ELSE 0 END) AS on_time_count,
ROUND((SUM(CASE WHEN s.actual_delivery <= s.scheduled_delivery THEN 1.0 ELSE 0.0 END) /
N... | Chosen calculates exact OTD compliance percentage against SLA benchmarks with native ClickHouse (OLAP) date filter (s.scheduled_delivery >= subtractDays(now(), 20)) and NULLIF protection; Rejected uses raw timestamp differences without SLA threshold comparison. |
sql_sft_00041 | Enterprise SQL & Text-to-DB | Fintech & Core Banking | MySQL 8.0 | Advanced Relational Window & CTE Synthesis | High | - accounts (account_id UUID PRIMARY KEY, user_id UUID, currency VARCHAR(3), balance NUMERIC(18,2), account_type VARCHAR(20), status VARCHAR(15), opened_at TIMESTAMPTZ)
- transactions (txn_id UUID PRIMARY KEY, source_acc_id UUID, dest_acc_id UUID, amount NUMERIC(18,2), fee NUMERIC(10,2), status VARCHAR(20), txn_type VAR... | In MySQL 8.0, generate a production-ready analytical query to compute the top 11 largest completed transactions for each active 'ESCROW' account over the last 233 days. Include transaction rank, account currency, amount, and timestamp. | <thought>
1. Identify target tables: `accounts` (filtering `status = 'ACTIVE'`, `account_type = 'ESCROW'`), and `transactions`.
2. Time window: Native MySQL 8.0 syntax for 233 days: `t.created_at >= DATE_SUB(NOW(), INTERVAL 233 DAY)`.
3. Status filter: Ensure `transactions.status = 'COMPLETED'`.
4. Window Ranking: Use ... | <thought>
1. Identify target tables: `accounts` (filtering `status = 'ACTIVE'`, `account_type = 'ESCROW'`), and `transactions`.
2. Time window: Native MySQL 8.0 syntax for 233 days: `t.created_at >= DATE_SUB(NOW(), INTERVAL 233 DAY)`.
3. Status filter: Ensure `transactions.status = 'COMPLETED'`.
4. Window Ranking: Use ... | ```sql
SELECT a.account_id, t.txn_id, t.amount
FROM accounts a, transactions t
WHERE a.account_id = t.source_acc_id AND a.account_type = 'ESCROW'
ORDER BY t.amount DESC LIMIT 11;
``` | WITH ranked_txns AS (
SELECT
a.account_id, a.user_id, a.currency, t.txn_id, t.amount, t.created_at,
DENSE_RANK() OVER (PARTITION BY a.account_id ORDER BY t.amount DESC, t.created_at DESC) AS txn_rank
FROM accounts a
INNER JOIN transactions t ON a.account_id = t.source_acc_id
WHERE a.sta... | Chosen uses native MySQL 8.0 window partitioning to get top 11 per individual account; Rejected applies an unpartitioned global LIMIT across all accounts combined. |
sql_sft_00042 | Enterprise SQL & Text-to-DB | SaaS & Subscription Analytics | MySQL 8.0 | Advanced Relational Window & CTE Synthesis | High | - workspaces (workspace_id UUID PRIMARY KEY, owner_id UUID, company_name VARCHAR(100), plan_tier VARCHAR(20), mrr NUMERIC(10,2), seats_allocated INT, created_at TIMESTAMPTZ)
- subscription_invoices (invoice_id UUID PRIMARY KEY, workspace_id UUID, amount_billed NUMERIC(10,2), payment_status VARCHAR(20), billing_period_s... | In MySQL 8.0, compose an optimized query returning 'SCALE' workspaces with at least 55 allocated seats where average user event activity has dropped by more than 50% over the last 92 days compared to prior period. | <thought>
1. Tables: `workspaces`, `user_activity_events`.
2. Filter: `workspaces.plan_tier = 'SCALE'` and `seats_allocated >= 55`.
3. Compare two time windows: `e.created_at >= DATE_SUB(NOW(), INTERVAL 92 DAY)` vs prior period.
</thought> | <thought>
1. Tables: `workspaces`, `user_activity_events`.
2. Filter: `workspaces.plan_tier = 'SCALE'` and `seats_allocated >= 55`.
3. Compare two time windows: `e.created_at >= DATE_SUB(NOW(), INTERVAL 92 DAY)` vs prior period.
</thought>
```sql
WITH activity_summary AS (
SELECT
w.workspace_id,
w... | ```sql
SELECT w.workspace_id, COUNT(e.event_id)
FROM workspaces w, user_activity_events e
WHERE w.workspace_id = e.workspace_id
GROUP BY w.workspace_id;
``` | WITH activity_summary AS (
SELECT
w.workspace_id,
w.company_name,
w.seats_allocated,
w.mrr,
COUNT(CASE WHEN e.created_at >= DATE_SUB(NOW(), INTERVAL 92 DAY) THEN 1 END) AS current_events,
COUNT(CASE WHEN e.created_at >= DATE_SUB(NOW(), INTERVAL 184 DAY) AND NOT (e.cr... | Chosen uses native MySQL 8.0 conditional aggregation to compare historical time periods with NULLIF division protection; Rejected does an unconstrained join without date windows. |
sql_sft_00043 | Enterprise SQL & Text-to-DB | E-Commerce & Global Retail | MySQL 8.0 | Advanced Relational Window & CTE Synthesis | High | - customers (customer_id UUID PRIMARY KEY, email VARCHAR(150), country VARCHAR(2), loyalty_tier VARCHAR(20), lifetime_spend NUMERIC(12,2), first_order_at TIMESTAMPTZ, is_active BOOL)
- orders (order_id UUID PRIMARY KEY, customer_id UUID, status VARCHAR(20), gross_total NUMERIC(12,2), discount_amount NUMERIC(10,2), net_... | Using MySQL 8.0, write a CTE-based SQL query to extract all 'VIP' tier customers in 'NL' whose 326-day repeat purchase velocity exceeds 22297 USD, and list their total net spent. | <thought>
1. Tables: `customers`, `orders`.
2. Filters: `country = 'NL'`, `loyalty_tier = 'VIP'`, `orders.status IN ('DELIVERED', 'COMPLETED')`.
3. Date Filter (MySQL 8.0): `o.placed_at >= DATE_SUB(NOW(), INTERVAL 326 DAY)`.
4. Group by customer, calculate total net spend and order count.
</thought> | <thought>
1. Tables: `customers`, `orders`.
2. Filters: `country = 'NL'`, `loyalty_tier = 'VIP'`, `orders.status IN ('DELIVERED', 'COMPLETED')`.
3. Date Filter (MySQL 8.0): `o.placed_at >= DATE_SUB(NOW(), INTERVAL 326 DAY)`.
4. Group by customer, calculate total net spend and order count.
</thought>
```sql
WITH custom... | ```sql
SELECT c.customer_id, SUM(o.gross_total)
FROM customers c
JOIN orders o ON c.customer_id = o.customer_id
WHERE c.country = 'NL'
GROUP BY c.customer_id;
``` | WITH customer_spends AS (
SELECT
c.customer_id,
c.email,
COUNT(DISTINCT o.order_id) AS total_orders,
SUM(o.net_total) AS total_net_spent
FROM customers c
INNER JOIN orders o ON c.customer_id = o.customer_id
WHERE c.country = 'NL'
AND c.loyalty_tier = 'VIP'
AN... | Chosen filters by delivered orders and computes net revenue within native MySQL 8.0 timeframe (o.placed_at >= DATE_SUB(NOW(), INTERVAL 326 DAY)); Rejected sums gross revenue without status or date constraints. |
sql_sft_00044 | Enterprise SQL & Text-to-DB | Healthcare & Hospital Management | MySQL 8.0 | Advanced Relational Window & CTE Synthesis | High | - patients (patient_id UUID PRIMARY KEY, mrn VARCHAR(20), birth_date DATE, gender VARCHAR(10), blood_type VARCHAR(5), primary_physician_id UUID, created_at TIMESTAMPTZ)
- clinical_encounters (encounter_id UUID PRIMARY KEY, patient_id UUID, department VARCHAR(50), admission_time TIMESTAMPTZ, discharge_time TIMESTAMPTZ, ... | In MySQL 8.0, compose an optimized query returning the 57-day all-cause readmission rate and median length of stay in days for the 'ONCOLOGY' department across all inpatient encounters in 2024. | <thought>
1. Tables: `clinical_encounters`.
2. Department filter: `department = 'ONCOLOGY'`.
3. Length of stay: `EXTRACT(EPOCH FROM (discharge_time - admission_time)) / 86400.0`.
4. Readmission: `LEAD(admission_time) OVER (PARTITION BY patient_id ORDER BY admission_time)`.
5. Median percentile in MySQL 8.0: `PERCENTILE... | <thought>
1. Tables: `clinical_encounters`.
2. Department filter: `department = 'ONCOLOGY'`.
3. Length of stay: `EXTRACT(EPOCH FROM (discharge_time - admission_time)) / 86400.0`.
4. Readmission: `LEAD(admission_time) OVER (PARTITION BY patient_id ORDER BY admission_time)`.
5. Median percentile in MySQL 8.0: `PERCENTILE... | ```sql
SELECT department, COUNT(encounter_id), AVG(discharge_time - admission_time)
FROM clinical_encounters WHERE department = 'ONCOLOGY' GROUP BY department;
``` | WITH encounter_timelines AS (
SELECT
encounter_id,
patient_id,
admission_time,
discharge_time,
EXTRACT(EPOCH FROM (discharge_time - admission_time)) / 86400.0 AS los_days,
LEAD(admission_time) OVER (
PARTITION BY patient_id
ORDER BY admission... | Chosen accurately tracks patient readmissions via LEAD() within a 57-day window and uses native MySQL 8.0 median calculation (PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY los_days)); Rejected provides unadjusted averages without readmission tracking. |
sql_sft_00045 | Enterprise SQL & Text-to-DB | Logistics & Global Supply Chain | MySQL 8.0 | Advanced Relational Window & CTE Synthesis | High | - shipments (shipment_id UUID PRIMARY KEY, origin_hub_id UUID, dest_hub_id UUID, carrier_id UUID, status VARCHAR(25), weight_kg NUMERIC(10,2), scheduled_delivery TIMESTAMPTZ, actual_delivery TIMESTAMPTZ)
- tracking_milestones (milestone_id UUID PRIMARY KEY, shipment_id UUID, checkpoint_name VARCHAR(100), latitude NUMER... | Using MySQL 8.0, write an index-optimized query to retrieve logistics carriers handling shipments over 1470 kg whose actual On-Time Delivery (OTD) percentage fell below the contractual SLA target of 95.1% over the last 64 days. | <thought>
1. Tables: `shipments`, `carrier_sla_benchmarks`.
2. Filter: `weight_kg >= 1470`, status = 'DELIVERED'.
3. Time filter (MySQL 8.0): `s.scheduled_delivery >= DATE_SUB(NOW(), INTERVAL 64 DAY)`.
4. OTD: `actual_delivery <= scheduled_delivery`.
5. Group by carrier and calculate adherence percentage.
</thought> | <thought>
1. Tables: `shipments`, `carrier_sla_benchmarks`.
2. Filter: `weight_kg >= 1470`, status = 'DELIVERED'.
3. Time filter (MySQL 8.0): `s.scheduled_delivery >= DATE_SUB(NOW(), INTERVAL 64 DAY)`.
4. OTD: `actual_delivery <= scheduled_delivery`.
5. Group by carrier and calculate adherence percentage.
</thought>
`... | ```sql
SELECT carrier_id, AVG(actual_delivery - scheduled_delivery)
FROM shipments WHERE weight_kg >= 1470 GROUP BY carrier_id;
``` | WITH carrier_perf AS (
SELECT
s.carrier_id,
COUNT(s.shipment_id) AS total_shipments,
SUM(CASE WHEN s.actual_delivery <= s.scheduled_delivery THEN 1 ELSE 0 END) AS on_time_count,
ROUND((SUM(CASE WHEN s.actual_delivery <= s.scheduled_delivery THEN 1.0 ELSE 0.0 END) /
N... | Chosen calculates exact OTD compliance percentage against SLA benchmarks with native MySQL 8.0 date filter (s.scheduled_delivery >= DATE_SUB(NOW(), INTERVAL 64 DAY)) and NULLIF protection; Rejected uses raw timestamp differences without SLA threshold comparison. |
sql_sft_00046 | Enterprise SQL & Text-to-DB | Fintech & Core Banking | Google BigQuery | Advanced Relational Window & CTE Synthesis | High | - accounts (account_id UUID PRIMARY KEY, user_id UUID, currency VARCHAR(3), balance NUMERIC(18,2), account_type VARCHAR(20), status VARCHAR(15), opened_at TIMESTAMPTZ)
- transactions (txn_id UUID PRIMARY KEY, source_acc_id UUID, dest_acc_id UUID, amount NUMERIC(18,2), fee NUMERIC(10,2), status VARCHAR(20), txn_type VAR... | Using Google BigQuery, write a CTE-based SQL query to extract the daily net transaction inflow for currency 'USD' over the last 334 days. Filter out any transactions flagged with an AML risk score exceeding 92. | <thought>
1. Table selection: `transactions` with left joins to `accounts` for currency verification.
2. Inflow vs Outflow: Inflow is incoming (dest_acc_id), Outflow is outgoing (source_acc_id).
3. AML Exclusion: Use `NOT EXISTS` against `aml_screenings` with `risk_score > 92` to avoid dropping unscreened transactions.... | <thought>
1. Table selection: `transactions` with left joins to `accounts` for currency verification.
2. Inflow vs Outflow: Inflow is incoming (dest_acc_id), Outflow is outgoing (source_acc_id).
3. AML Exclusion: Use `NOT EXISTS` against `aml_screenings` with `risk_score > 92` to avoid dropping unscreened transactions.... | ```sql
SELECT CAST(t.created_at AS DATE), SUM(t.amount)
FROM transactions t
INNER JOIN aml_screenings s ON t.txn_id = s.txn_id
WHERE s.risk_score <= 92
GROUP BY 1;
``` | SELECT
CAST(t.created_at AS DATE) AS txn_date,
SUM(CASE WHEN a_dest.currency = 'USD' THEN t.amount ELSE 0 END) AS total_inflow,
SUM(CASE WHEN a_src.currency = 'USD' THEN t.amount ELSE 0 END) AS total_outflow,
SUM(CASE WHEN a_dest.currency = 'USD' THEN t.amount ELSE 0 END) -
SUM(CASE WHEN a_src.cur... | Chosen uses NOT EXISTS to keep clean unscreened transactions and separates inflow from outflow; Rejected uses INNER JOIN which drops all clean transactions that haven't been manually screened. |
sql_sft_00047 | Enterprise SQL & Text-to-DB | SaaS & Subscription Analytics | Google BigQuery | Advanced Relational Window & CTE Synthesis | High | - workspaces (workspace_id UUID PRIMARY KEY, owner_id UUID, company_name VARCHAR(100), plan_tier VARCHAR(20), mrr NUMERIC(10,2), seats_allocated INT, created_at TIMESTAMPTZ)
- subscription_invoices (invoice_id UUID PRIMARY KEY, workspace_id UUID, amount_billed NUMERIC(10,2), payment_status VARCHAR(20), billing_period_s... | Using Google BigQuery, generate a production-ready analytical query to compute the Month-over-Month MRR expansion and net contraction for 'GROWTH' workspaces over the last 261 days, using paid invoices. | <thought>
1. Tables: `workspaces` (filtering `plan_tier = 'GROWTH'`), `subscription_invoices` (`payment_status = 'PAID'`).
2. Monthly revenue aggregation in Google BigQuery: `DATE_TRUNC(i.billing_period_start, MONTH)`.
3. Time filter in Google BigQuery: `i.billing_period_start >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTE... | <thought>
1. Tables: `workspaces` (filtering `plan_tier = 'GROWTH'`), `subscription_invoices` (`payment_status = 'PAID'`).
2. Monthly revenue aggregation in Google BigQuery: `DATE_TRUNC(i.billing_period_start, MONTH)`.
3. Time filter in Google BigQuery: `i.billing_period_start >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTE... | ```sql
SELECT DATE_TRUNC(i.billing_period_start, MONTH), SUM(amount_billed)
FROM subscription_invoices WHERE payment_status = 'PAID' GROUP BY 1;
``` | WITH monthly_workspace_rev AS (
SELECT
w.workspace_id,
w.company_name,
DATE_TRUNC(i.billing_period_start, MONTH) AS billing_month,
SUM(i.amount_billed) AS current_mrr,
LAG(SUM(i.amount_billed)) OVER (
PARTITION BY w.workspace_id
ORDER BY DATE_TRUNC(i... | Chosen leverages native Google BigQuery date truncation (DATE_TRUNC(i.billing_period_start, MONTH)) and LAG() over cohorts; Rejected merges all revenue without cohort delta tracking. |
End of preview. Expand in Data Studio
💎 Enterprise SQL, Text-to-DB & Schema-Aware Query Architect SFT/DPO Dataset (2026)
High-precision instruction tuning and preference optimization dataset with step-by-step Chain-of-Thought (<thought>) reasoning chains for fine-tuning LLMs (Llama-3.3, Qwen-2.5-Coder, DeepSeek-R1-Distill, Mistral, Phi-4) into Senior Enterprise Database Architects.
📊 Dataset Overview
- Format: OpenAI ChatML & ShareGPT format with
<thought>reasoning tokens. - Dialect Coverage (5 Native Engines):
PostgreSQL 16,Snowflake SQL,ClickHouse (OLAP),MySQL 8.0,Google BigQuery. - Enterprise Schemas (5 Domains):
Fintech & Core Banking,SaaS Analytics,E-Commerce Retail,Healthcare EMR, andLogistics & Supply Chain. - Preference Optimization (DPO): 2,500 curated Chosen vs Rejected pairs with automated failure critiques (Cartesian product prevention, timezone traps, indexability).
- Colab Support: Bundled 1-Click Unsloth Google Colab Notebook (
1_CLICK_UNSLOTH_SQL_FINE_TUNING_COLAB.ipynb) for fast LoRA training on free T4 GPU.
🚀 Quick Start with Hugging Face datasets
from datasets import load_dataset
# Load from Parquet
dataset = load_dataset("parquet", data_files="ENTERPRISE_SQL_TEXT_TO_DB_2026_100_SAMPLE.parquet", split="train")
print(f"Loaded {len(dataset)} sample SQL training instances!")
print("Sample Query:", dataset[0]["sql_query_clean"])
👑 Get the Complete 10,000 SFT + 2,500 DPO Enterprise Suite
Need the complete production-grade package with 10,000 SFT pairs, 2,500 DPO pairs, full 41.8 MB SQLite DB, and commercial enterprise deployment rights?
- Downloads last month
- 65