Country or region and language

    Choose your country or region and your language.

    Suggested for you

      All countries and regions

        Choose your character

        It shows in the top bar and on your profile. Change it any time.

        SQL interview questions for 5 years of experience

        These 10 questions progress from defining a row to handling concurrent writes, with model answers, production follow-ups, and two runnable PostgreSQL tasks.

        A person practicing on a laptop in a quiet workspace

        These 10 questions progress from defining a row to handling concurrent writes, with model answers, production follow-ups, and two runnable PostgreSQL tasks.

        Experienced answers beyond syntax

        Strong answers explain why a solution stays correct when the data changes. Every SQL example below uses PostgreSQL, all invoice data is fictional, and amounts represent Philippine pesos.

        Before proposing a solution, establish:

        • What one row represents and which columns identify it.
        • Which revisions and statuses count toward the result.
        • What should happen when records are missing, repeated, or changed concurrently.

        Row meaning and missing values

        Row definitions and missing-value rules determine whether keys and totals mean what the business expects.

        1. Interviewer: "An invoice table stores revisions. What does one row represent, and what key would you choose?"
        Model answer: One row represents one invoice revision. Invoice number plus revision identifies a revision within one company. Enforce uniqueness and make both columns non-null. A separate row ID doesn't replace business-key uniqueness.
        Production follow-up: "What if invoice numbers repeat across companies?"
        Answer: Include company ID in the unique key and in joins that identify an invoice.
        What the interviewer listens for: A precise row definition and keys with the correct scope.
        
        2. Interviewer: "How do counting rows, counting an amount column, and summing amounts treat nulls?"
        Model answer: COUNT(*) counts rows. COUNT(amount) counts non-null amounts. SUM(amount) ignores null inputs and returns null when there are no non-null inputs.
        Production follow-up: "Would you replace every missing amount with zero?"
        Answer: No. Zero is a known value; null can mean unknown. Confirm the reporting rule and count missing amounts separately when completeness matters.
        What the interviewer listens for: Correct null behavior and a distinction between missing data and zero.

        Practice what this post teaches

        Join cardinality and current revisions

        Join cardinality describes how many rows can match across tables, and it determines whether aggregation is safe.

        3. Interviewer: "An invoice has three lines and two payments. Why does joining both child tables inflate totals?"
        Model answer: The join creates six line-payment combinations. Each line appears twice; each payment appears three times. Aggregate each child table to one row per invoice before joining. Removing duplicates isn't a safe fix because legitimate transactions can have equal amounts.
        Production follow-up: "What if payments also have revisions?"
        Answer: Select each payment's current revision first, then aggregate current payments by invoice.
        What the interviewer listens for: Recognition of row multiplication and correction at the appropriate row level.

        For the first practical task, paste the complete query into a PostgreSQL query editor. A window function calculates across related rows without collapsing those rows; the ranking below restarts for each invoice.

        4. Interviewer: "Return the latest revision of each invoice, including canceled invoices."
        
        Fictional exercise specification
        Database: PostgreSQL. Scope: one company.
        Target: Exactly one current revision per invoice.
        Rule: Highest revision wins; invoice ID plus revision is unique.
        Starting state: No saved tables required; rows are defined below.
        Amounts: Philippine pesos. Starting changes: None.
        Model selection: Invoice 101 revision 2; invoice 102 revision 2.
        
        WITH invoice_revisions (invoice_id, revision, status, amount) AS (
          VALUES
            (101, 1, 'posted', 1000),
            (101, 2, 'posted', 1200),
            (102, 1, 'posted', 800),
            (102, 2, 'canceled', 800)
        ), ranked AS (
          SELECT invoice_id, revision, status, amount,
                 ROW_NUMBER() OVER (
                   PARTITION BY invoice_id ORDER BY revision DESC
                 ) AS rn
          FROM invoice_revisions
        )
        SELECT invoice_id, revision, status, amount
        FROM ranked
        WHERE rn = 1
        ORDER BY invoice_id;
        
        Expected output:
        invoice_id | revision | status   | amount
        101        | 2        | posted   | 1200
        102        | 2        | canceled | 800
        
        Model answer: Rank revisions within each invoice, newest first, then keep rank one.
        Production follow-up: "What if revision numbers tie?"
        Answer: Enforce the stated uniqueness rule. If ties are allowed, establish business precedence before choosing a tie-breaker.
        What the interviewer listens for: Correct partitioning and deterministic selection under explicit constraints.

        Filter order and query structure

        Resolve revision history before filtering status, or an older posted revision can replace a newer cancellation. Run the next query independently, then make the specified input change to test the cancellation rule.

        5. Interviewer: "Calculate current posted revenue. Where does the status filter belong?"
        
        Fictional exercise specification
        Database: PostgreSQL. Scope: one company.
        Target: Sum amounts whose latest invoice revision is posted.
        Rule: Highest revision wins; invoice ID plus revision is unique.
        Report convention: No qualifying amounts produces zero.
        Starting state: No saved tables required; rows are defined below.
        Amounts: Philippine pesos. Starting changes: None.
        Model calculation: Invoice 101 contributes 1200; canceled invoice
        102 contributes nothing. Total = 1200.
        
        WITH invoice_revisions (invoice_id, revision, status, amount) AS (
          VALUES
            (101, 1, 'posted', 1000),
            (101, 2, 'posted', 1200),
            (102, 1, 'posted', 800),
            (102, 2, 'canceled', 800)
        ), ranked AS (
          SELECT invoice_id, status, amount,
                 ROW_NUMBER() OVER (
                   PARTITION BY invoice_id ORDER BY revision DESC
                 ) AS rn
          FROM invoice_revisions
        )
        SELECT COALESCE(SUM(amount), 0) AS posted_revenue
        FROM ranked
        WHERE rn = 1 AND status = 'posted';
        
        Expected output:
        posted_revenue
        1200
        
        Model answer: Rank all revisions, keep the current revision, then filter posted status. COALESCE supplies the report's zero convention; it doesn't validate missing amounts.
        Production follow-up: "How would you verify that cancellation affects revenue?"
        Answer and exact change: In the VALUES block, replace only
        (102, 2, 'canceled', 800)
        with
        (102, 2, 'posted', 800)
        Then rerun the complete query above.
        Changed model calculation: 1200 + 800 = 2000.
        Expected changed output:
        posted_revenue
        2000
        
        What the interviewer listens for: Correct filter order and a controlled test with an expected result.

        Try explaining your filter order before opening SQL interview practice. The Work Gym checks exercises automatically with a hint that names the mistake. Its Interview Hub supports typed and spoken practice with a written report.

        6. Interviewer: "Would a common table expression make this query faster?"
        Model answer: A common table expression, or CTE, names a query result within a statement. It improves readability, not necessarily speed. PostgreSQL can fold some CTEs into the surrounding query; other cases are evaluated separately. Compare execution plans and results.
        Production follow-up: "Would you use a temporary table?"
        Answer: Possibly for reuse across statements or separate indexes. Measure creation and loading as well as the final query.
        What the interviewer listens for: Separation of readable structure from measured optimization.

        Performance evidence

        Performance decisions need execution plans, representative data, and measurements rather than assumptions about indexes.

        7. Interviewer: "Which index would you consider for one company's invoice revisions, newest first?"
        Model answer: Consider a B-tree index on company ID, invoice ID, then revision. Equality conditions narrow the invoice lookup. PostgreSQL can scan a B-tree backward for descending revision order. Check existing unique indexes before adding another.
        Production follow-up: "Would you add similar indexes everywhere?"
        Answer: No. Check access patterns and plans. Indexes consume storage and add maintenance work to writes.
        What the interviewer listens for: Column-order reasoning and read-versus-write trade-offs.
        
        8. Interviewer: "A revenue query slowed as data grew. How would you investigate?"
        Model answer: Capture query parameters, runtime, and data volume. Inspect the execution plan, which describes how PostgreSQL processes the query. Compare estimated and actual row counts; inspect row multiplication, repeated work, and disk spills. Check statistics and indexes, then change one factor and compare correctness and runtime.
        Production follow-up: "Would you collect actual execution measurements for any production statement?"
        Answer: No. Actual measurements execute the statement. Assess risk and use an appropriate test environment, especially for writes. A sequential scan isn't automatically wrong when much of a table is needed.
        What the interviewer listens for: Reproducible diagnosis and operational caution.

        Concurrent writes and repeat processing

        Concurrent processing requires rules that prevent conflicting updates and repeated business effects, not just correct selection queries.

        9. Interviewer: "Two workers read the same invoice revision and attempt the next revision. How would you prevent a conflict?"
        Model answer: Enforce revision uniqueness. Within a transaction, lock a stable parent invoice row before reading and appending the next revision. Alternatively, use optimistic concurrency: change a stored version only if it matches the version read, and check success. Retry or reject conflicts explicitly.
        Production follow-up: "Does PostgreSQL's default isolation keep consecutive reads unchanged?"
        Answer: No. Read committed isolation uses a new snapshot per statement. Protect the whole business operation. Locking an existing revision doesn't lock a future revision.
        What the interviewer listens for: Concrete transaction boundaries and conflict handling.
        
        10. Interviewer: "An import delivers an event twice, and a worker can crash before acknowledgment. How would you prevent double processing?"
        Model answer: Use idempotency: repeating an event mustn't repeat its effect. Enforce a unique source event ID in a processing ledger. In one transaction, record a new event and apply its database effect only when newly recorded. Commit before acknowledgment. A retry then finds the existing event.
        Production follow-up: "What if an event ID arrives with a different amount, or processing sends a notification?"
        Answer: Compare stored event content and reject conflicting reuse. For notifications, store a pending notification in the same transaction, then deliver separately with retries and deduplication. A database transaction doesn't make external delivery atomic.
        What the interviewer listens for: Event identity, atomic database changes, and explicit handling of external effects.

        The small invoice example proves result logic, not production speed or concurrency safety. Rehearse with each answer hidden: state the rule, solution, expected result, and one condition that invalidates the solution. Performance and locking tests need representative data and concurrent sessions.

        Common questions

        How advanced should SQL answers be with five years of experience? Go beyond producing valid syntax. Explain row meaning, edge cases, verification, and production trade-offs without assuming that experience makes every database system familiar.

        Which SQL dialect should I use in an interview? Ask which database system the task uses, then name your dialect before writing. These examples use PostgreSQL; don't assume optimizer or isolation behavior transfers unchanged to other systems.

        How should I respond when a business rule is unclear? Ask a specific question, such as whether a cancellation replaces the previous posted revision. If clarification isn't available, state your assumption and explain how a different rule changes the solution.

        How can I practice performance questions without a production database? Use a test database with fictional data and compare plans as data volume and distribution change. Small datasets help check correctness, but they don't establish production performance.

        Practice what this post teaches

        Your Work Gym account

        Save your progress and pick up on another device. Your browser's progress comes with you.

        or

        You can keep practicing without an account.

        Help and feedback

        Ask a question, tell us what is not working, or share an idea. We reply by email.

        What is it about?

        We use it only to reply to you.