Scorecard · Plainsight

Measured on gpt-5.4-mini, the model this site answers with, one run per row.

85%with table and column names only

100%with written notes about the data (+15 points)

7 → 15hard questions right, of 15, once the notes are added

10 of 10unanswerable questions refused, with notes

Accuracy by what the model was given

The notes do the work. Worked examples and a second try add nothing measurable on top: a difference of one question is within run-to-run noise.

Table and column names only 85%
+ written notes about the data 100%
+ worked examples 98%
+ second attempt if the SQL fails 100%
Everything, on deepseek-v4-flash 95%
Everything, on deepseek-v4-pro 98%

The same ladder on other models

The ladder was first measured on DeepSeek, then again on gpt-5.4-mini. The jump comes from the notes on both.

What the model was given	gpt-5.4-mini	deepseek-v4-pro
Table and column names only	85%	83%
+ written notes about the data	100%	98%
+ worked examples	98%	97%
+ second attempt if the SQL fails	100%	98%

Right answers by difficulty

Hard questions depend on a business rule or a trap in the data. Unanswerable questions should be refused.

Run	Easy	Medium	Hard	Unanswerable	All
Table and column names only	15 of 15	20 of 20	7 of 15	9 of 10	85%
+ written notes about the data	15 of 15	20 of 20	15 of 15	10 of 10	100%
+ worked examples	15 of 15	19 of 20	15 of 15	10 of 10	98%
+ second attempt if the SQL fails	15 of 15	20 of 20	15 of 15	10 of 10	100%
Everything, on deepseek-v4-flash	15 of 15	17 of 20	15 of 15	10 of 10	95%
Everything, on deepseek-v4-pro	15 of 15	19 of 20	15 of 15	10 of 10	98%

What went wrong

What is the total revenue?hard

Why it failed: wrong definition: summed payments (includes freight and undelivered orders) instead of item prices

Generated

SELECT SUM(payment_value) AS total_revenue
FROM order_payments

Reference

SELECT sum(i.price) FROM order_items i JOIN orders o ON i.order_id = o.order_id WHERE o.order_status = 'delivered'

Which 5 categories have the highest revenue? Return the category and its revenue.hard

Why it failed: wrong filter: included orders that were never delivered

Generated

SELECT
  COALESCE(t.product_category_name_english, p.product_category_name) AS category,
  SUM(oi.price) AS revenue
FROM order_items oi
JOIN products p
  ON oi.product_id = p.product_id
LEFT JOIN product_category_name_translation t
  ON p.product_category_name = t.product_category_name
GROUP BY 1
ORDER BY revenue DESC
LIMIT 5

Reference

SELECT t.product_category_name_english, sum(i.price) AS revenue FROM order_items i JOIN orders o ON i.order_id = o.order_id JOIN products p ON i.product_id = p.product_id JOIN product_category_name_translation t ON p.product_category_name = t.product_category_name WHERE o.order_status = 'delivered' GROUP BY 1 ORDER BY revenue DESC LIMIT 5

Which month had the highest revenue? Return the month as YYYY-MM and the revenue.hard

Why it failed: wrong filter: included orders that were never delivered

Generated

SELECT strftime('%Y-%m', o.order_purchase_timestamp) AS month, SUM(oi.price) AS revenue
FROM orders o
JOIN order_items oi ON o.order_id = oi.order_id
GROUP BY 1
ORDER BY revenue DESC
LIMIT 1

Reference

SELECT strftime(o.order_purchase_timestamp, '%Y-%m') AS month, sum(i.price) AS revenue FROM order_items i JOIN orders o ON i.order_id = o.order_id WHERE o.order_status = 'delivered' GROUP BY month ORDER BY revenue DESC LIMIT 1

What is the total payment value of orders that contain at least one item in the health_beauty category?hard

Why it failed: wrong column: filtered on the Portuguese category name, where health_beauty doesn't exist, so the total was empty

Generated

SELECT SUM(op.payment_value) AS total_payment_value
FROM order_payments op
JOIN (
    SELECT DISTINCT oi.order_id
    FROM order_items oi
    JOIN products p ON oi.product_id = p.product_id
    WHERE p.product_category_name = 'health_beauty'
) hb_orders
ON op.order_id = hb_orders.order_id

Reference

SELECT sum(payment_value) FROM order_payments WHERE order_id IN (
  SELECT i.order_id FROM order_items i
  JOIN products p ON i.product_id = p.product_id
  JOIN product_category_name_translation t ON p.product_category_name = t.product_category_name
  WHERE t.product_category_name_english = 'health_beauty')

Which 3 sellers have the highest revenue? Return the seller id and the revenue.hard

Why it failed: wrong filter: included orders that were never delivered

Generated

SELECT
  seller_id,
  SUM(price) AS revenue
FROM order_items
GROUP BY seller_id
ORDER BY revenue DESC
LIMIT 3

Reference

SELECT i.seller_id, sum(i.price) AS revenue FROM order_items i JOIN orders o ON i.order_id = o.order_id WHERE o.order_status = 'delivered' GROUP BY i.seller_id ORDER BY revenue DESC LIMIT 3

How many customers are in the state SP?hard

Why it failed: wrong key: counted customer rows (a new customer_id for every order), not distinct people

Generated

SELECT COUNT(*) AS customer_count
FROM customers
WHERE customer_state = 'SP'

Reference

SELECT count(DISTINCT customer_unique_id) FROM customers WHERE customer_state = 'SP'

For each year of purchase, which customer state had the highest revenue? Return the year, the state and the revenue.hard

Why it failed: wrong definition: added freight to revenue and kept undelivered orders

Generated

SELECT purchase_year AS year, customer_state AS state, revenue
FROM (
    SELECT
        EXTRACT(YEAR FROM o.order_purchase_timestamp) AS purchase_year,
        c.customer_state,
        SUM(oi.price + oi.freight_value) AS revenue,
        ROW_NUMBER() OVER (
            PARTITION BY EXTRACT(YEAR FROM o.order_purchase_timestamp)
            ORDER BY SUM(oi.price + oi.freight_value) DESC, c.customer_state
        ) AS rn
    FROM orders o
    JOIN customers c ON o.customer_id = c.customer_id
    JOIN order_items oi ON o.order_id = oi.order_id
    GROUP BY 1, 2
) t
WHERE rn = 1
ORDER BY year

Reference

SELECT yr, customer_state, revenue FROM (
  SELECT year(o.order_purchase_timestamp) AS yr, c.customer_state, sum(i.price) AS revenue,
         row_number() OVER (PARTITION BY year(o.order_purchase_timestamp) ORDER BY sum(i.price) DESC) AS rn
  FROM order_items i
  JOIN orders o ON i.order_id = o.order_id
  JOIN customers c ON o.customer_id = c.customer_id
  WHERE o.order_status = 'delivered'
  GROUP BY 1, 2)
WHERE rn = 1

For how many orders does the total payment value differ from the total of item prices plus freight by more than 1? Only consider orders that have both items and payments.hard

Why it failed: query error: used a table alias it never defined; this setup has no second try

Generated

SELECT COUNT(*) AS order_count
FROM (
    SELECT
        oi.order_id,
        ABS(COALESCE(pi.total_payment_value, 0) - COALESCE(ii.total_item_value, 0)) AS diff
    FROM (
        SELECT order_id, SUM(price + freight_value) AS total_item_value
        FROM order_items
        GROUP BY order_id
    ) ii
    INNER JOIN (
        SELECT order_id, SUM(payment_value) AS total_payment_value
        FROM order_payments
        GROUP BY order_id
    ) pi
    ON ii.order_id = pi.order_id
) t
WHERE diff > 1

Reference

SELECT count(*) FROM
  (SELECT order_id, sum(price + freight_value) AS item_total FROM order_items GROUP BY order_id) i
  JOIN (SELECT order_id, sum(payment_value) AS paid FROM order_payments GROUP BY order_id) p
  ON i.order_id = p.order_id
WHERE abs(i.item_total - p.paid) > 1

Error: Binder Error: Referenced table "oi" not found! Candidate tables: "ii", "pi" LINE 4: oi.order_id, ^^^^^^^^^^^

What was the cost of goods sold in 2018?unanswerable

Why it failed: answered the unanswerable: called item prices the cost of goods sold, but the data has no costs

Generated

SELECT SUM(oi.price) AS cost_of_goods_sold
FROM order_items oi
JOIN orders o ON oi.order_id = o.order_id
WHERE o.order_purchase_timestamp >= TIMESTAMP '2018-01-01'
  AND o.order_purchase_timestamp < TIMESTAMP '2019-01-01'

Reference

CANNOT_ANSWER