Articles

CoderPad SQL Interview: Explore the Schema, Manage Database State, and Explain the Query

Prepare for a CoderPad SQL interview with schema probes, join-grain checks, stateful database runs, reset precautions, and an explained query with edge cases.

A CoderPad SQL interview tests how you turn an unfamiliar database into a query another person can understand. Start by identifying the SQL engine, reading the schema, and confirming the row grain. Then run small read-only probes before writing the final aggregation. A query that executes successfully can still answer the wrong question or depend on data changed by an earlier run.

Official platform boundary: CoderPad Interview supports attached custom MySQL and PostgreSQL databases. Its database documentation says mutations persist within an interview session; another execution is not an automatic restoration of the original data. Treat the supplied pad and instructions as the authority for the environment you actually have.

This guide separates provider facts, one scoped candidate report, and original SQL practice.

What should you inspect before writing the main query?

Ask which product and language mode the invitation uses. CoderPad Interview, Screen and a multi-file application are different contexts. This article focuses on a SQL database in Interview; it does not establish a universal duration, question count or employer evaluation policy.

Official interface fact: The database documentation describes Raw and Explorer schema views available to both candidate and interviewer. Use the diagram to locate relationships and the raw definition to check actual columns, types and constraints. A connecting line does not tell you whether every apparent relationship is one-to-one in the data.

Identify the output grain before inspecting every table. “Paid revenue per customer, including customers with no paid orders” asks for one row per customer. An order-item table is at a different grain. Joining it unnecessarily can multiply the order total while producing a result that looks plausible.

On PostgreSQL, a documentation-reviewed metadata probe is:

SELECT table_name, column_name, data_type, is_nullable
FROM information_schema.columns
WHERE table_schema = 'public'
ORDER BY table_name, ordinal_position;

The PostgreSQL information schema reference explains that this view exposes accessible columns and their metadata. Adjust the schema name to the supplied database. This is a PostgreSQL example, not proof of the version or permissions in every CoderPad pad.

Which small probes reveal the data's real grain?

Our original exercise has three customers: A, B and C. There are five orders and six item rows. A has paid orders 10 and 11 with totals 30 and 20, plus cancelled order 12 for 100. B has cancelled order 20 for 70. Order 30 is paid for 10 but has a missing customer reference. C has no orders.

Order 10 has two item rows; each other order has one. The supplied amounts are non-null integer units. We deliberately allow the missing customer reference so that the discovery step has an observable data-quality issue. This fixture is not CoderPad's default sample database or a reported interview question.

Start with row counts, key checks and missing references. For example:

SELECT order_id, COUNT(*) AS copies
FROM orders
GROUP BY order_id
HAVING COUNT(*) > 1;

SELECT COUNT(*) AS missing_customer_references
FROM orders o
LEFT JOIN customers c ON c.customer_id = o.customer_id
WHERE c.customer_id IS NULL;

In our fixture, the first probe returns no rows and the second returns 1. That combination matters: unique order IDs do not guarantee valid customer references. Also inspect duplicates on the key you plan to join with, rather than only on a primary key that the database already enforces.

Explain why each probe changes your plan. A duplicate event key may require an agreed deduplication policy. A missing amount may make a revenue result unknown rather than zero. A timestamp range can reveal that the dataset covers only part of the requested period. Exploration earns its time when it resolves an assumption needed by the final query.

Why can a valid join inflate revenue or lose customers?

The desired customer results are A=50, B=0 and C=0. The orphan order is a separate quality finding; it is excluded from the customer report because it cannot match a listed customer. State that consequence rather than silently treating the dataset as reconciled.

Joining paid orders to items before summing order totals produces A=80: order 10's 30 is counted twice and order 11's 20 once. The SQL engine has correctly evaluated the join. The mistake is summing a parent-level measure after moving to item-level rows.

Adding SUM(DISTINCT total) is not a general repair. Two different paid orders can legitimately have the same total. Deduplicating their amounts would discard revenue. Restore the intended grain by aggregating orders before joining, or omit the item table when it contributes no required information.

Predicate placement creates a second failure. A left join followed by WHERE o.status = 'paid' removes customers without a matching paid order. Filtering the joined orders in ON, or filtering an orders-only aggregation first, preserves the requested customer population. PostgreSQL's SELECT reference documents join and filtering semantics; the 50-versus-80 example is our original counterexample.

Also distinguish COUNT(*) from counting a matched order ID. In a left join, an unmatched customer still has a result row. Counting every result row can report one order for C. Inspect a row before aggregation when the output is surprising; it often reveals the mismatch faster than adding another clause.

How do you explain a correct query while writing it?

This query keeps the paid-order aggregation at one row per customer reference, then joins that result to the required customer population:

WITH paid AS (
    SELECT customer_id,
           COUNT(*) AS paid_order_count,
           SUM(total) AS paid_total
    FROM orders
    WHERE status = 'paid'
    GROUP BY customer_id
)
SELECT c.customer_id,
       COALESCE(p.paid_order_count, 0) AS paid_order_count,
       COALESCE(p.paid_total, 0) AS paid_total
FROM customers c
LEFT JOIN paid p ON p.customer_id = c.customer_id
ORDER BY c.customer_id;

The output is A/2/50, B/0/0 and C/0/0. Say what each stage guarantees. The CTE filters paid orders and reduces their grain. The customer table supplies the required population. The left join preserves customers without paid activity. The explicit ordering makes the demonstration reproducible without relying on incidental row order.

Official SQL distinction: PostgreSQL aggregates treat COUNT(*), non-null expression counts and SUM differently; a sum over no input rows is null. In this fixture, amounts cannot be missing, and the business contract defines no paid orders as zero. That is why the final COALESCE is justified. Do not reuse the expression to disguise unknown individual amounts in a different dataset.

Narrate the property, not every keystroke. “I aggregate before joining because one order can have multiple items” communicates a decision. “Now I type GROUP BY” does not. If the interviewer asks for product revenue instead, revisit the measure and grain; item-level amounts may become the appropriate source.

What survives another Run, a rollback or a Reset?

Official CoderPad behavior: A custom database is copied for a new interview session, while inserts, updates and deletes remain stateful within that session. The database documentation describes Reset as restoring the original database. Do not confuse rerunning a SELECT with restarting the supplied fixture.

Our original local run ledger exposes the difference:

Action	A's paid total	Explanation
Fresh fixture, then SELECT	50	Original amounts 30 and 20
Add 1000 to order 10 and commit	1050	Data changed; query stayed the same
Run only the SELECT again	1050	Reading does not undo the mutation
Run the additive UPDATE again	2050	The mutation itself is not idempotent
Rebuild the original local fixture	50	Explicit restoration, not another query run

The ledger was executed on local SQLite with explicit transaction boundaries. It demonstrates persistent SQL state, not a recording of CoderPad buttons or a PostgreSQL server. We also executed an update inside a transaction and rolled it back: the intermediate total was 1050 and the restored total 50.

PostgreSQL's transaction tutorial explains BEGIN, COMMIT and ROLLBACK. Whether a pad's runner keeps a transaction open across separate UI executions is a separate environment question. Do not promise that leaving BEGIN in one run and typing ROLLBACK later will work without confirming the runner's connection and execution behavior.

For interview exploration, prefer SELECT unless the task actually asks you to modify data. If mutation is required, identify the affected rows, explain the recovery boundary and keep the setup distinguishable from the answer. A setup script mixed into every execution can repeatedly add data or fail when a table already exists.

How should you recover when the environment changes?

Official provider caution: The candidate preparation guide warns that changing languages can make an attached custom database unavailable. The PostgreSQL environment page also describes database reset on Reset or language changes. Its published version label is not a guarantee for every customer pad; confirm the actual engine before using version-specific syntax.

If an answer changes unexpectedly, first inspect a stable control: row counts, the altered order and the query text. Decide whether the difference came from database state, connection context or query semantics. A newly missing table after changing language is not evidence that your join is wrong.

Before a broad reset, explain what you expect it to restore and which useful mutations or outputs it may discard. Keep the current query and a short record of the state you need to reproduce. The generic preparation guide discusses clearing output; the database-specific documentation describes restoring data. Use the latter for SQL state instead of assuming Reset only clears the display.

If a command fails, retain the exact error and reduce the experiment. Check names, schema qualification, dialect and supported functions before translating an entire query. An engine-specific metadata command should not be treated as portable merely because the query editor accepts SQL text.