Casagbic fan-out test: all sixteen prompts, exactly as sent. System prompt for every call: You write SQL for SQLite. ============================================================================== Task 1: one fan-out (orders and line items) | bare schema ============================================================================== SQLite schema: CREATE TABLE customers ( id INTEGER PRIMARY KEY, name TEXT NOT NULL ); CREATE TABLE orders ( id INTEGER PRIMARY KEY, customer_id INTEGER NOT NULL REFERENCES customers(id), order_total INTEGER NOT NULL ); CREATE TABLE order_items ( id INTEGER PRIMARY KEY, order_id INTEGER NOT NULL REFERENCES orders(id), qty INTEGER NOT NULL, line_amount INTEGER NOT NULL ); Write one SQLite query that returns one row per customer with exactly these columns: name, revenue, units. revenue is the total amount charged to the customer. units is the total number of units the customer bought. Reply with only the query, in a single ```sql code block. ============================================================================== Task 1: one fan-out (orders and line items) | documented schema ============================================================================== SQLite schema: CREATE TABLE customers ( id INTEGER PRIMARY KEY, name TEXT NOT NULL ); CREATE TABLE orders ( id INTEGER PRIMARY KEY, customer_id INTEGER NOT NULL REFERENCES customers(id), order_total INTEGER NOT NULL -- amount charged for the whole order, in dollars ); CREATE TABLE order_items ( id INTEGER PRIMARY KEY, order_id INTEGER NOT NULL REFERENCES orders(id), -- an order has one or more lines qty INTEGER NOT NULL, -- units on this line line_amount INTEGER NOT NULL -- dollars for this line ); Write one SQLite query that returns one row per customer with exactly these columns: name, revenue, units. revenue is the total amount charged to the customer. units is the total number of units the customer bought. Reply with only the query, in a single ```sql code block. ============================================================================== Task 2: two fan-outs plus an order with no payments | bare schema ============================================================================== SQLite schema: CREATE TABLE customers ( id INTEGER PRIMARY KEY, name TEXT NOT NULL ); CREATE TABLE orders ( id INTEGER PRIMARY KEY, customer_id INTEGER NOT NULL REFERENCES customers(id), order_total INTEGER NOT NULL ); CREATE TABLE order_items ( id INTEGER PRIMARY KEY, order_id INTEGER NOT NULL REFERENCES orders(id), qty INTEGER NOT NULL, line_amount INTEGER NOT NULL ); CREATE TABLE payments ( id INTEGER PRIMARY KEY, order_id INTEGER NOT NULL REFERENCES orders(id), amount INTEGER NOT NULL ); Write one SQLite query that returns one row per customer with exactly these columns: name, charged, paid, units. charged is the total amount charged to the customer across all their orders. paid is the total of payments received from the customer. units is the total number of units the customer bought. Reply with only the query, in a single ```sql code block. ============================================================================== Task 2: two fan-outs plus an order with no payments | documented schema ============================================================================== SQLite schema: CREATE TABLE customers ( id INTEGER PRIMARY KEY, name TEXT NOT NULL ); CREATE TABLE orders ( id INTEGER PRIMARY KEY, customer_id INTEGER NOT NULL REFERENCES customers(id), order_total INTEGER NOT NULL -- amount charged for the whole order, in dollars ); CREATE TABLE order_items ( id INTEGER PRIMARY KEY, order_id INTEGER NOT NULL REFERENCES orders(id), -- an order has one or more lines qty INTEGER NOT NULL, -- units on this line line_amount INTEGER NOT NULL -- dollars for this line ); CREATE TABLE payments ( id INTEGER PRIMARY KEY, order_id INTEGER NOT NULL REFERENCES orders(id), -- an order can have zero, one or many payments amount INTEGER NOT NULL -- dollars received in this payment ); Write one SQLite query that returns one row per customer with exactly these columns: name, charged, paid, units. charged is the total amount charged to the customer across all their orders. paid is the total of payments received from the customer. units is the total number of units the customer bought. Reply with only the query, in a single ```sql code block. ============================================================================== Task 2: two fan-outs plus an order with no payments | keyless schema ============================================================================== SQLite schema: CREATE TABLE customers ( id INTEGER, name TEXT ); CREATE TABLE orders ( id INTEGER, customer_id INTEGER, order_total INTEGER ); CREATE TABLE order_items ( id INTEGER, order_id INTEGER, qty INTEGER, line_amount INTEGER ); CREATE TABLE payments ( id INTEGER, order_id INTEGER, amount INTEGER ); Write one SQLite query that returns one row per customer with exactly these columns: name, charged, paid, units. charged is the total amount charged to the customer across all their orders. paid is the total of payments received from the customer. units is the total number of units the customer bought. Reply with only the query, in a single ```sql code block. ============================================================================== Task 3: summary table joined to a history table and a lookup | bare schema ============================================================================== SQLite schema: CREATE TABLE region_benchmarks ( region TEXT PRIMARY KEY, target_repayment_ratio REAL NOT NULL ); CREATE TABLE country_debt_summary ( country TEXT PRIMARY KEY, region TEXT NOT NULL REFERENCES region_benchmarks(region), outstanding_debt INTEGER NOT NULL ); CREATE TABLE repayments ( id INTEGER PRIMARY KEY, country TEXT NOT NULL REFERENCES country_debt_summary(country), paid_on TEXT NOT NULL, amount INTEGER NOT NULL ); Write one SQLite query that returns one row per region with exactly these columns: region, total_debt, total_repaid, target_repayment_ratio. total_debt is the sum of outstanding debt for the countries in the region. total_repaid is the sum of all repayments made by those countries. Reply with only the query, in a single ```sql code block. ============================================================================== Task 3: summary table joined to a history table and a lookup | documented schema ============================================================================== SQLite schema: CREATE TABLE region_benchmarks ( region TEXT PRIMARY KEY, -- one row per region target_repayment_ratio REAL NOT NULL ); CREATE TABLE country_debt_summary ( country TEXT PRIMARY KEY, -- one row per country region TEXT NOT NULL REFERENCES region_benchmarks(region), outstanding_debt INTEGER NOT NULL -- total outstanding debt for the country, USD millions ); CREATE TABLE repayments ( id INTEGER PRIMARY KEY, country TEXT NOT NULL REFERENCES country_debt_summary(country), -- a country has many repayments paid_on TEXT NOT NULL, -- date of the repayment amount INTEGER NOT NULL -- USD millions ); Write one SQLite query that returns one row per region with exactly these columns: region, total_debt, total_repaid, target_repayment_ratio. total_debt is the sum of outstanding debt for the countries in the region. total_repaid is the sum of all repayments made by those countries. Reply with only the query, in a single ```sql code block. ============================================================================== Task 4: wide report with a count and an average | bare schema ============================================================================== SQLite schema: CREATE TABLE customers ( id INTEGER PRIMARY KEY, name TEXT NOT NULL ); CREATE TABLE orders ( id INTEGER PRIMARY KEY, customer_id INTEGER NOT NULL REFERENCES customers(id), order_total INTEGER NOT NULL ); CREATE TABLE order_items ( id INTEGER PRIMARY KEY, order_id INTEGER NOT NULL REFERENCES orders(id), qty INTEGER NOT NULL, line_amount INTEGER NOT NULL ); CREATE TABLE payments ( id INTEGER PRIMARY KEY, order_id INTEGER NOT NULL REFERENCES orders(id), amount INTEGER NOT NULL ); Write one SQLite query that returns one row per customer with exactly these columns: name, orders, charged, avg_order, paid, units. orders is the number of orders the customer placed. charged is the total amount charged to the customer across all their orders. avg_order is the average amount charged per order. paid is the total of payments received from the customer. units is the total number of units the customer bought. Reply with only the query, in a single ```sql code block. ============================================================================== Task 4: wide report with a count and an average | documented schema ============================================================================== SQLite schema: CREATE TABLE customers ( id INTEGER PRIMARY KEY, name TEXT NOT NULL ); CREATE TABLE orders ( id INTEGER PRIMARY KEY, customer_id INTEGER NOT NULL REFERENCES customers(id), order_total INTEGER NOT NULL -- amount charged for the whole order, in dollars ); CREATE TABLE order_items ( id INTEGER PRIMARY KEY, order_id INTEGER NOT NULL REFERENCES orders(id), -- an order has one or more lines qty INTEGER NOT NULL, -- units on this line line_amount INTEGER NOT NULL -- dollars for this line ); CREATE TABLE payments ( id INTEGER PRIMARY KEY, order_id INTEGER NOT NULL REFERENCES orders(id), -- an order can have zero, one or many payments amount INTEGER NOT NULL -- dollars received in this payment ); Write one SQLite query that returns one row per customer with exactly these columns: name, orders, charged, avg_order, paid, units. orders is the number of orders the customer placed. charged is the total amount charged to the customer across all their orders. avg_order is the average amount charged per order. paid is the total of payments received from the customer. units is the total number of units the customer bought. Reply with only the query, in a single ```sql code block. ============================================================================== Task 4: wide report with a count and an average | keyless schema ============================================================================== SQLite schema: CREATE TABLE customers ( id INTEGER, name TEXT ); CREATE TABLE orders ( id INTEGER, customer_id INTEGER, order_total INTEGER ); CREATE TABLE order_items ( id INTEGER, order_id INTEGER, qty INTEGER, line_amount INTEGER ); CREATE TABLE payments ( id INTEGER, order_id INTEGER, amount INTEGER ); Write one SQLite query that returns one row per customer with exactly these columns: name, orders, charged, avg_order, paid, units. orders is the number of orders the customer placed. charged is the total amount charged to the customer across all their orders. avg_order is the average amount charged per order. paid is the total of payments received from the customer. units is the total number of units the customer bought. Reply with only the query, in a single ```sql code block. ============================================================================== Task 5: a country total repeated on every detail row | bare schema ============================================================================== SQLite schema: CREATE TABLE region_benchmarks ( region TEXT PRIMARY KEY, target_repayment_ratio REAL NOT NULL ); CREATE TABLE debt_positions ( country TEXT NOT NULL, region TEXT NOT NULL REFERENCES region_benchmarks(region), creditor_type TEXT NOT NULL, outstanding_amount INTEGER NOT NULL, country_total_debt INTEGER NOT NULL, PRIMARY KEY (country, creditor_type) ); Write one SQLite query that returns one row per region with exactly these columns: region, total_debt, countries, target_repayment_ratio. total_debt is the total outstanding debt of the countries in the region. countries is the number of countries in the region. Reply with only the query, in a single ```sql code block. ============================================================================== Task 5: a country total repeated on every detail row | documented schema ============================================================================== SQLite schema: CREATE TABLE region_benchmarks ( region TEXT PRIMARY KEY, -- one row per region target_repayment_ratio REAL NOT NULL ); CREATE TABLE debt_positions ( country TEXT NOT NULL, region TEXT NOT NULL REFERENCES region_benchmarks(region), creditor_type TEXT NOT NULL, -- one row per country and creditor type outstanding_amount INTEGER NOT NULL, -- debt owed to this creditor type, USD millions country_total_debt INTEGER NOT NULL, -- the country's total across all creditor types, repeated on each of its rows PRIMARY KEY (country, creditor_type) ); Write one SQLite query that returns one row per region with exactly these columns: region, total_debt, countries, target_repayment_ratio. total_debt is the total outstanding debt of the countries in the region. countries is the number of countries in the region. Reply with only the query, in a single ```sql code block. ============================================================================== Task 5: a country total repeated on every detail row | keyless schema ============================================================================== SQLite schema: CREATE TABLE region_benchmarks ( region TEXT, target_repayment_ratio REAL ); CREATE TABLE debt_positions ( country TEXT, region TEXT, creditor_type TEXT, outstanding_amount INTEGER, country_total_debt INTEGER ); Write one SQLite query that returns one row per region with exactly these columns: region, total_debt, countries, target_repayment_ratio. total_debt is the total outstanding debt of the countries in the region. countries is the number of countries in the region. Reply with only the query, in a single ```sql code block. ============================================================================== Task 6: nine tables, four child tables, seven metrics | bare schema ============================================================================== SQLite schema: CREATE TABLE segments ( id INTEGER PRIMARY KEY, label TEXT NOT NULL ); CREATE TABLE customers ( id INTEGER PRIMARY KEY, name TEXT NOT NULL, segment_id INTEGER REFERENCES segments(id) ); CREATE TABLE products ( id INTEGER PRIMARY KEY, sku TEXT NOT NULL, category TEXT NOT NULL ); CREATE TABLE orders ( id INTEGER PRIMARY KEY, customer_id INTEGER NOT NULL REFERENCES customers(id), placed_on TEXT NOT NULL, order_total INTEGER NOT NULL ); CREATE TABLE order_items ( id INTEGER PRIMARY KEY, order_id INTEGER NOT NULL REFERENCES orders(id), product_id INTEGER NOT NULL REFERENCES products(id), qty INTEGER NOT NULL, line_amount INTEGER NOT NULL ); CREATE TABLE payments ( id INTEGER PRIMARY KEY, order_id INTEGER NOT NULL REFERENCES orders(id), received_on TEXT NOT NULL, amount INTEGER NOT NULL ); CREATE TABLE refunds ( id INTEGER PRIMARY KEY, order_id INTEGER NOT NULL REFERENCES orders(id), issued_on TEXT NOT NULL, amount INTEGER NOT NULL ); CREATE TABLE shipments ( id INTEGER PRIMARY KEY, order_id INTEGER NOT NULL REFERENCES orders(id), shipped_on TEXT NOT NULL, carrier TEXT NOT NULL ); CREATE TABLE support_tickets ( id INTEGER PRIMARY KEY, customer_id INTEGER NOT NULL REFERENCES customers(id), opened_on TEXT NOT NULL ); Write one SQLite query that returns one row per customer with exactly these columns: name, orders, charged, refunded, net_revenue, paid, units, shipments. orders is the number of orders the customer placed. charged is the total amount charged to the customer across all their orders. refunded is the total amount refunded to the customer. net_revenue is charged minus refunded. paid is the total of payments received from the customer. units is the total number of units the customer bought. shipments is the number of shipments sent to the customer. Reply with only the query, in a single ```sql code block. ============================================================================== Task 6: nine tables, four child tables, seven metrics | documented schema ============================================================================== SQLite schema: CREATE TABLE segments ( id INTEGER PRIMARY KEY, label TEXT NOT NULL ); CREATE TABLE customers ( id INTEGER PRIMARY KEY, name TEXT NOT NULL, segment_id INTEGER REFERENCES segments(id) ); CREATE TABLE products ( id INTEGER PRIMARY KEY, sku TEXT NOT NULL, category TEXT NOT NULL ); CREATE TABLE orders ( id INTEGER PRIMARY KEY, customer_id INTEGER NOT NULL REFERENCES customers(id), placed_on TEXT NOT NULL, order_total INTEGER NOT NULL -- amount charged for the whole order, in dollars ); CREATE TABLE order_items ( id INTEGER PRIMARY KEY, order_id INTEGER NOT NULL REFERENCES orders(id), -- an order has one or more lines product_id INTEGER NOT NULL REFERENCES products(id), qty INTEGER NOT NULL, -- units on this line line_amount INTEGER NOT NULL -- dollars for this line ); CREATE TABLE payments ( id INTEGER PRIMARY KEY, order_id INTEGER NOT NULL REFERENCES orders(id), -- an order can have zero, one or many payments received_on TEXT NOT NULL, amount INTEGER NOT NULL -- dollars received in this payment ); CREATE TABLE refunds ( id INTEGER PRIMARY KEY, order_id INTEGER NOT NULL REFERENCES orders(id), -- an order can have zero, one or many refunds issued_on TEXT NOT NULL, amount INTEGER NOT NULL -- dollars refunded ); CREATE TABLE shipments ( id INTEGER PRIMARY KEY, order_id INTEGER NOT NULL REFERENCES orders(id), -- an order can have zero, one or many shipments shipped_on TEXT NOT NULL, carrier TEXT NOT NULL ); CREATE TABLE support_tickets ( id INTEGER PRIMARY KEY, customer_id INTEGER NOT NULL REFERENCES customers(id), opened_on TEXT NOT NULL ); Write one SQLite query that returns one row per customer with exactly these columns: name, orders, charged, refunded, net_revenue, paid, units, shipments. orders is the number of orders the customer placed. charged is the total amount charged to the customer across all their orders. refunded is the total amount refunded to the customer. net_revenue is charged minus refunded. paid is the total of payments received from the customer. units is the total number of units the customer bought. shipments is the number of shipments sent to the customer. Reply with only the query, in a single ```sql code block. ============================================================================== Task 6: nine tables, four child tables, seven metrics | keyless schema ============================================================================== SQLite schema: CREATE TABLE segments ( id INTEGER, label TEXT ); CREATE TABLE customers ( id INTEGER, name TEXT, segment_id INTEGER ); CREATE TABLE products ( id INTEGER, sku TEXT, category TEXT ); CREATE TABLE orders ( id INTEGER, customer_id INTEGER, placed_on TEXT, order_total INTEGER ); CREATE TABLE order_items ( id INTEGER, order_id INTEGER, product_id INTEGER, qty INTEGER, line_amount INTEGER ); CREATE TABLE payments ( id INTEGER, order_id INTEGER, received_on TEXT, amount INTEGER ); CREATE TABLE refunds ( id INTEGER, order_id INTEGER, issued_on TEXT, amount INTEGER ); CREATE TABLE shipments ( id INTEGER, order_id INTEGER, shipped_on TEXT, carrier TEXT ); CREATE TABLE support_tickets ( id INTEGER, customer_id INTEGER, opened_on TEXT ); Write one SQLite query that returns one row per customer with exactly these columns: name, orders, charged, refunded, net_revenue, paid, units, shipments. orders is the number of orders the customer placed. charged is the total amount charged to the customer across all their orders. refunded is the total amount refunded to the customer. net_revenue is charged minus refunded. paid is the total of payments received from the customer. units is the total number of units the customer bought. shipments is the number of shipments sent to the customer. Reply with only the query, in a single ```sql code block.