Interview Prep

SQL Joins Practice Exercises: 15 Real Queries & Answers

Master SQL joins with 15 real business practice exercises. Solve INNER, LEFT, RIGHT, FULL OUTER, CROSS, and SELF JOINs with schemas and expected outputs.

Anuj SainiSep 12, 202631 min read

In relational database systems, raw data is distributed across dozens of normalized tables to eliminate redundancy and maintain referential integrity. As an analyst or analytics engineer, your primary responsibility is assembling these decoupled tables back into unified, high-integrity analytical views.

Textbooks often introduce SQL joins using overlapping two-circle Venn diagrams. While Venn diagrams provide a basic geometric intuition for set overlap, they fail to explain true relational database operations. Real relational joins operate on Cartesian coordinate pairs, handle Cartesian fan-out, evaluate null-propagation flags, and require strict join predicate positioning.

This practice guide delivers 15 production-grade SQL join exercises across five progressively challenging tiers:

  1. Level 1: INNER JOIN: Exact relational matching, multi-table order histories, and subscription billing runs.
  2. Level 2: LEFT JOIN & Anti-Joins: Preserving base grains, detecting unengaged customers, and isolating dormant catalog inventory.
  3. Level 3: RIGHT & FULL OUTER JOIN: Bidirectional ledger auditing, physical warehouse reconciliation, and catalog gap detection.
  4. Level 4: SELF JOIN & Hierarchical Data: Managerial organizational trees, peer compensation variances, and consecutive activity detection.
  5. Level 5: Advanced Multi-Table & Non-Equi Joins: Fan-out mitigation with Common Table Expressions (CTEs), promo code date-range matching, and Cartesian calendar spines.

For broader interview coverage, explore our companion compilation of 25 Real Business SQL Practice Questions or master fundamental querying in Topfolio SQL Basics Course. Every course on Topfolio is 100% free to learn, with an optional ₹99 verified certificate to validate your skills.


Visual Relational Architecture

The exercises in this guide query the enterprise schema below. Pay close attention to primary keys (PK), foreign keys (FK), and relationship cardinalities.

+----------------------------------------------------------------------------------------------------+
|                                    RELATIONAL JOIN ARCHITECTURE                                    |
+----------------------------------------------------------------------------------------------------+

    [ suppliers ]                              [ products ]
    +-------------------------+                 +-------------------------+
    | supplier_id (PK)        |<----------------| supplier_id (FK)        |
    | supplier_name VARCHAR   |                 | product_id (PK)         |
    | contact_email VARCHAR   |                 | product_name VARCHAR    |
    | country CHAR(2)         |                 | category VARCHAR        |
    +-------------------------+                 | price NUMERIC           |
                                                | stock_quantity INT      |
                                                +-------------------------+
                                                             ^
                                                             | (Foreign Key)
    [ customers ]              [ orders ]                    |
    +--------------------+     +--------------------+        |
    | customer_id (PK)   |<----| customer_id (FK)   |        |
    | first_name VARCHAR |     | order_id (PK)      |        |
    | last_name VARCHAR  |     | order_date DATE    |        |
    | email VARCHAR      |     | status VARCHAR     |        |
    | city VARCHAR       |     | total_amount NUM   |        |
    | state CHAR(2)      |     +--------------------+        |
    +--------------------+               ^                   |
                                         |                   |
                               [ order_items ]               |
                               +---------------------+       |
                               | order_item_id (PK)  |       |
                               | order_id (FK)-------+       |
                               | product_id (FK)-------------+
                               | quantity INT        |
                               | unit_price NUMERIC  |
                               +---------------------+

    [ employees ] (Self-Referencing Manager Hierarchy)
    +--------------------------------------------------------------------+
    | employee_id (PK) | first_name | last_name | department | salary    |
    | manager_id (FK) ---------------------------------------+ (Self-FK) |
    +--------------------------------------------------------|-----------+
                                                             v

Critical Cardinality Rules:

  • customers to orders (1:N): One customer places zero, one, or many orders.
  • orders to order_items (1:N): One order contains one or more line items.
  • products to order_items (1:N): A product appears in zero or multiple order items.
  • employees to employees (1:N Self-Join): One manager supervises multiple employees; employees have at most one manager.

SQL Join Operators Mechanics & NULL Behaviors

Before solving exercises, examine how PostgreSQL handles unmatched rows, NULL propagation, and join filters across each join type:

SQL Join Engine Execution Behaviors
Feature / Criteria

Part 1: Relational Foundations & Join Mechanics

Understanding joins requires looking at how relational engines process predicates during execution. When two tables join, PostgreSQL logically builds an intermediate virtual table.

The ON Clause vs The WHERE Clause Filter Trap

The single most common bug in intermediate SQL interviews involves putting filter predicates on the right table inside the WHERE clause instead of the ON clause:

sql
-- WRONG: Inadvertently turns a LEFT JOIN into an INNER JOIN!
SELECT c.customer_id, c.first_name, o.order_id, o.status
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id
WHERE o.status = 'completed';

Why this fails: A customer who has never ordered generates a row where o.status IS NULL. The WHERE o.status = 'completed' filter evaluates NULL = 'completed', which yields FALSE in three-valued SQL logic. As a result, all customers with zero orders are silently eliminated!

sql
-- CORRECT: Preserves all customers while filtering joined orders
SELECT c.customer_id, c.first_name, o.order_id, o.status
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id
                   AND o.status = 'completed';

In the correct version, if a customer has no completed orders, the customer row is still preserved with NULL in the order fields. For a deeper study of logical execution order, review our guide to SQL Order of Execution.


Part 2: 15 Comprehensive SQL Join Exercises

Each exercise includes sample input data, the business problem statement, the PostgreSQL query, an explanation of the join mechanics, and the expected output table.


Exercise 1: Customer Order History & Fulfillment Status (INNER JOIN)

Business Scenario: The customer support portal requires an order lookup tool. For all orders with a status of 'completed' or 'shipped', retrieve the customer's full name, email address, order ID, order date, and total dollar amount.

Input Tables:

customers (sample rows):

customer_idfirst_namelast_nameemailcitystate
101ElenaRostovaelena.r@example.comSan FranciscoCA
102MarcusVancemarcus.v@example.comAustinTX
103ChloeBennettchloe.b@example.comSeattleWA

orders (sample rows):

order_idcustomer_idorder_datestatustotal_amount
50011012024-03-15completed340.50
50021022024-03-16pending120.00
50031012024-04-02shipped89.99
50041042024-04-10completed510.00

PostgreSQL Solution Query:

sql
SELECT 
    c.first_name || ' ' || c.last_name AS customer_name,
    c.email,
    o.order_id,
    o.order_date,
    o.status,
    o.total_amount
FROM customers AS c
INNER JOIN orders AS o ON c.customer_id = o.customer_id
WHERE o.status IN ('completed', 'shipped')
ORDER BY o.order_date DESC;

Logic Breakdown:

  • c.customer_id = o.customer_id matches customers to their specific orders.
  • Customer 102 placed order 5002, but its status is 'pending', so it is excluded by the WHERE clause.
  • Order 5004 belongs to customer 104 (not in our customer table snippet); an INNER JOIN discards any order lacking a valid matching customer record.

Expected Output Table:

customer_nameemailorder_idorder_datestatustotal_amount
Elena Rostovaelena.r@example.com50032024-04-02shipped89.99
Elena Rostovaelena.r@example.com50012024-03-15completed340.50

Exercise 2: Supplier Catalog & Product Sourcing (INNER JOIN)

Business Scenario: Procurement needs to renegotiate vendor contracts. Generate a list of all active products whose unit price exceeds $100.00, showing product name, category, unit price, supplier company name, and supplier contact email.

Input Tables:

suppliers (sample rows):

supplier_idsupplier_namecontact_emailcountry
10Apex Tech Componentssales@apextech.comUS
20Nordic Sound Labscontact@nordicsound.seSE
30Vertex Furnishingssupply@vertex.comUS

products (sample rows):

product_idproduct_namesupplier_idcategorypricestock_quantity
14K Ultra Monitor10Displays420.0045
2Noise-Cancel Headset20Audio189.50120
3Basic Mouse Pad10Accessories14.99300
4Standing Desk Pro30Furniture550.0018

PostgreSQL Solution Query:

sql
SELECT 
    p.product_id,
    p.product_name,
    p.category,
    p.price,
    s.supplier_name,
    s.contact_email
FROM products AS p
INNER JOIN suppliers AS s ON p.supplier_id = s.supplier_id
WHERE p.price > 100.00
ORDER BY p.price DESC;

Logic Breakdown:

  • Joins the child table products to parent table suppliers on p.supplier_id = s.supplier_id.
  • The Basic Mouse Pad matches supplier 10, but its price ($14.99) fails the p.price > 100.00 filter.
  • Returns only high-value catalog items linked with verified supplier contact information.

Expected Output Table:

product_idproduct_namecategorypricesupplier_namecontact_email
4Standing Desk ProFurniture550.00Vertex Furnishingssupply@vertex.com
14K Ultra MonitorDisplays420.00Apex Tech Componentssales@apextech.com
2Noise-Cancel HeadsetAudio189.50Nordic Sound Labscontact@nordicsound.se

Exercise 3: Active Subscription Billing Run (Multi-Table INNER JOIN)

Business Scenario: SaaS Finance needs to audit the monthly recurring billing batch. Join user accounts, subscription records, and plan tiers to calculate the monthly invoice amount for all users whose subscription status is 'active'.

Input Tables:

users (sample rows):

user_iduser_namebilling_email
801Sarah Connorsconnor@cyberdyne.org
802Kyle Reesekreese@resistance.net
803John Connorleader@future.org

subscriptions (sample rows):

sub_iduser_idplan_idstatusstart_date
90018011active2024-01-01
90028022cancelled2023-11-15
90038033active2024-02-20

plans (sample rows):

plan_idplan_namemonthly_feeseat_limit
1Starter29.002
2Professional79.0010
3Enterprise249.0050

PostgreSQL Solution Query:

sql
SELECT 
    u.user_id,
    u.user_name,
    u.billing_email,
    p.plan_name,
    p.monthly_fee
FROM users AS u
INNER JOIN subscriptions AS s ON u.user_id = s.user_id
INNER JOIN plans AS p ON s.plan_id = p.plan_id
WHERE s.status = 'active'
ORDER BY p.monthly_fee DESC;

Logic Breakdown:

  • Two sequential INNER JOIN operations: users links to subscriptions on user_id, and subscriptions links to plans on plan_id.
  • User 802 has a subscription record, but its status is 'cancelled', so it is excluded by the WHERE clause.
  • Only active accounts with valid plan configurations are output.

Expected Output Table:

user_iduser_namebilling_emailplan_namemonthly_fee
803John Connorleader@future.orgEnterprise249.00
801Sarah Connorsconnor@cyberdyne.orgStarter29.00

Exercise 4: Unengaged Customer Detection (LEFT Anti-Join)

Business Scenario: Lifecycle marketing is preparing a re-engagement email series. Identify all registered customers who have never placed an order. Return their customer ID, full name, email, and registration date.

Input Tables:

customers (sample rows):

customer_idfirst_namelast_nameemailsignup_date
201ArthurDentadent@galaxy.co.uk2023-05-12
202FordPrefectfprefect@betelgeuse.com2023-06-18
203TriciaMcMillantricia@earth.org2023-08-01
204MarvinAndroidmarvin@sirius.com2023-09-14

orders (sample rows):

order_idcustomer_idorder_datetotal_amount
70012012023-06-0142.00
70022032023-08-15118.50
70032012023-11-2065.00

PostgreSQL Solution Query:

sql
SELECT 
    c.customer_id,
    c.first_name || ' ' || c.last_name AS customer_name,
    c.email,
    c.signup_date
FROM customers AS c
LEFT JOIN orders AS o ON c.customer_id = o.customer_id
WHERE o.order_id IS NULL
ORDER BY c.signup_date ASC;

Logic Breakdown:

  • The LEFT JOIN preserves every customer in the output. For customers without matching orders (Ford Prefect and Marvin Android), the engine creates a virtual row where all columns from orders are set to NULL.
  • The predicate WHERE o.order_id IS NULL filters out customers who have placed orders, leaving only the unengaged cohort.
  • This is the standard relational anti-join pattern.

Expected Output Table:

customer_idcustomer_nameemailsignup_date
202Ford Prefectfprefect@betelgeuse.com2023-06-18
204Marvin Androidmarvin@sirius.com2023-09-14

Exercise 5: Dormant Catalog Inventory / Products Never Ordered (LEFT Anti-Join)

Business Scenario: Warehouse inventory managers want to identify dormant stock. Find all products in the catalog that have never been purchased in any order line item. Display the product ID, product name, category, unit price, and current stock count.

Input Tables:

products (sample rows):

product_idproduct_namecategorypricestock_quantity
501Thunderbolt Docking StationHardware249.0045
502Cat6 Ethernet Cable 10ftAccessories12.50400
503Retro Mechanical KeycapsAccessories45.0080
504Ergonomic FootrestFurniture65.0025

order_items (sample rows):

order_item_idorder_idproduct_idquantityunit_price
180015011249.00
28002502312.50
380035012249.00

PostgreSQL Solution Query:

sql
SELECT 
    p.product_id,
    p.product_name,
    p.category,
    p.price,
    p.stock_quantity
FROM products AS p
LEFT JOIN order_items AS oi ON p.product_id = oi.product_id
WHERE oi.order_item_id IS NULL
ORDER BY p.stock_quantity DESC;

Logic Breakdown:

  • Joins products to order_items using LEFT JOIN.
  • Products 501 and 502 have matching line items, so oi.order_item_id contains numeric IDs.
  • Products 503 and 504 have zero sales history, yielding NULL for oi.order_item_id.
  • The WHERE oi.order_item_id IS NULL clause extracts the dormant inventory items.

Expected Output Table:

product_idproduct_namecategorypricestock_quantity
503Retro Mechanical KeycapsAccessories45.0080
504Ergonomic FootrestFurniture65.0025

Exercise 6: Churn Detection with ON-Clause Temporal Predicates (LEFT JOIN)

Business Scenario: Retention managers need a list of active enterprise accounts that have logged zero user events over the past 30 days (from cutoff date 2024-04-30).

Input Tables:

accounts (sample rows):

account_idcompany_nameplan_type
1Acme LogisticsEnterprise
2Globex IndustrialEnterprise
3Initech SoftwareEnterprise

usage_events (sample rows):

event_idaccount_idevent_nameevent_date
9011export_report2024-04-18
9022api_sync2024-02-10
9031dashboard_view2024-04-29

PostgreSQL Solution Query:

sql
SELECT 
    a.account_id,
    a.company_name,
    a.plan_type
FROM accounts AS a
LEFT JOIN usage_events AS e 
    ON a.account_id = e.account_id
   AND e.event_date >= '2024-04-01'
   AND e.event_date <= '2024-04-30'
WHERE a.plan_type = 'Enterprise'
  AND e.event_id IS NULL
ORDER BY a.account_id ASC;

Logic Breakdown:

  • The date range filter is intentionally placed inside the ON clause. This ensures accounts with events outside the 30-day window (like Globex, whose event was in February) evaluate to NULL for the join without getting filtered out of the left table.
  • Account 1 has events inside April, so e.event_id is not null.
  • Accounts 2 and 3 have no April events; their e.event_id is null, correctly flagging them as inactive.

Expected Output Table:

account_idcompany_nameplan_type
2Globex IndustrialEnterprise
3Initech SoftwareEnterprise

Practice SQL Joins in Your Live Browser Sandbox

Run queries against live PostgreSQL databases with automated verification on Topfolio. Free to learn, optional ₹99 verified certificate.

Start Free SQL Course

Exercise 7: Warehouse Inventory vs Central Catalog Audit (FULL OUTER JOIN)

Business Scenario: A supply chain team is reconciling physical warehouse scanner counts against the central ERP product catalog. Identify all discrepancies: products present in the catalog but missing from warehouse scans, and uncataloged barcode items found in the warehouse.

Input Tables:

central_catalog (sample rows):

skuproduct_titlecatalog_price
SKU-100Bluetooth Earbuds49.99
SKU-200Mechanical Keyboard119.00
SKU-3001080p Webcam65.00

warehouse_inventory (sample rows):

skuphysical_countbay_location
SKU-100350A-12
SKU-200110B-04
SKU-99915Z-99

PostgreSQL Solution Query:

sql
SELECT 
    COALESCE(c.sku, w.sku) AS resolved_sku,
    c.product_title,
    c.catalog_price,
    w.physical_count,
    w.bay_location,
    CASE 
        WHEN c.sku IS NOT NULL AND w.sku IS NOT NULL THEN 'Matched / Verified'
        WHEN c.sku IS NOT NULL AND w.sku IS NULL THEN 'Missing in Warehouse'
        WHEN c.sku IS NULL AND w.sku IS NOT NULL THEN 'Uncataloged Inventory'
    END AS audit_status
FROM central_catalog AS c
FULL OUTER JOIN warehouse_inventory AS w ON c.sku = w.sku
ORDER BY resolved_sku ASC;

Logic Breakdown:

  • A FULL OUTER JOIN preserves all rows from both tables.
  • COALESCE(c.sku, w.sku) returns the valid SKU regardless of which side matched.
  • The CASE WHEN statement uses NULL checks to categorize each item into one of three audit buckets: verified, missing stock, or untracked stock.

Expected Output Table:

resolved_skuproduct_titlecatalog_pricephysical_countbay_locationaudit_status
SKU-100Bluetooth Earbuds49.99350A-12Matched / Verified
SKU-200Mechanical Keyboard119.00110B-04Matched / Verified
SKU-3001080p Webcam65.00NULLNULLMissing in Warehouse
SKU-999NULLNULL15Z-99Uncataloged Inventory

Exercise 8: Omnichannel Transaction Ledger Reconciliation (FULL OUTER JOIN)

Business Scenario: Financial audit requires reconciling payment processor credit card authorizations against bank settlement deposits. Find all unmatched transaction references (authorized charges with no bank deposit, or bank deposits with no matching processor authorization).

Input Tables:

gateway_transactions (sample rows):

ref_numauth_amountauth_time
TXN-101150.002024-04-01 10:14:00
TXN-10242.502024-04-01 11:30:00
TXN-103310.002024-04-01 14:22:00

bank_settlements (sample rows):

ref_numsettled_amountdeposit_date
TXN-101150.002024-04-02
TXN-103310.002024-04-02
TXN-88895.002024-04-02

PostgreSQL Solution Query:

sql
SELECT 
    COALESCE(g.ref_num, b.ref_num) AS reconciliation_reference,
    g.auth_amount,
    b.settled_amount,
    CASE 
        WHEN g.ref_num IS NULL THEN 'Unmatched Bank Deposit'
        WHEN b.ref_num IS NULL THEN 'Unsettled Gateway Charge'
    END AS discrepancy_reason
FROM gateway_transactions AS g
FULL OUTER JOIN bank_settlements AS b ON g.ref_num = b.ref_num
WHERE g.ref_num IS NULL OR b.ref_num IS NULL
ORDER BY reconciliation_reference ASC;

Logic Breakdown:

  • The FULL OUTER JOIN pairs matching transaction references.
  • The WHERE g.ref_num IS NULL OR b.ref_num IS NULL clause strips away matched transactions (TXN-101 and TXN-103), isolating only the unbalanced ledger exceptions.

Expected Output Table:

reconciliation_referenceauth_amountsettled_amountdiscrepancy_reason
TXN-10242.50NULLUnsettled Gateway Charge
TXN-888NULL95.00Unmatched Bank Deposit

Exercise 9: Regional Sales Territories vs Active Sales Reps (RIGHT JOIN)

Business Scenario: Sales leadership is restructuring geographic regions. List all designated sales territories along with the name of the assigned sales representative. Include any territory that currently has no assigned rep.

Input Tables:

sales_reps (sample rows):

rep_idrep_nameterritory_id
1Alice Johnson10
2Bob Martinez20
3Carol Danvers10

territories (sample rows):

territory_idterritory_nametarget_quota
10Pacific Northwest500000
20Southwest Desert350000
30Great Lakes450000

PostgreSQL Solution Query:

sql
SELECT 
    t.territory_id,
    t.territory_name,
    t.target_quota,
    COALESCE(r.rep_name, 'Unassigned Territory') AS assigned_rep
FROM sales_reps AS r
RIGHT JOIN territories AS t ON r.territory_id = t.territory_id
ORDER BY t.territory_id ASC;

Logic Breakdown:

  • The RIGHT JOIN guarantees that every record in territories is preserved in the output, even if no sales rep is assigned to that territory ID.
  • Territory 30 (Great Lakes) has no matching row in sales_reps, so r.rep_name evaluates to NULL.
  • COALESCE provides a clean label for unassigned regions.
  • Senior Engineering Tip: Most data teams refactor RIGHT JOIN queries to LEFT JOIN (FROM territories t LEFT JOIN sales_reps r ...) because reading queries from left to right improves code maintainability.

Expected Output Table:

territory_idterritory_nametarget_quotaassigned_rep
10Pacific Northwest500000Alice Johnson
10Pacific Northwest500000Carol Danvers
20Southwest Desert350000Bob Martinez
30Great Lakes450000Unassigned Territory

Exercise 10: Complete Management Reporting Hierarchy (SELF JOIN)

Business Scenario: HR requires a company directory mapping every employee to their direct supervisor. The top executive has no manager (manager_id IS NULL) and should display 'Executive Leadership'.

Input Table:

employees (sample rows):

employee_idfirst_namelast_namedepartmentmanager_id
1BruceWayneExecutiveNULL
2LuciusFoxEngineering1
3DickGraysonOperations1
4TimDrakeEngineering2
5BarbaraGordonAnalytics2

PostgreSQL Solution Query:

sql
SELECT 
    e.employee_id,
    e.first_name || ' ' || e.last_name AS employee_name,
    e.department,
    COALESCE(m.first_name || ' ' || m.last_name, 'Executive Leadership') AS manager_name
FROM employees AS e
LEFT JOIN employees AS m ON e.manager_id = m.employee_id
ORDER BY e.employee_id ASC;

Logic Breakdown:

  • A self-join queries the same table twice using two distinct table aliases: e represents the individual employee, and m represents their respective manager.
  • The join condition e.manager_id = m.employee_id links the subordinate's foreign key to the supervisor's primary key.
  • A LEFT JOIN preserves the CEO (Bruce Wayne), whose manager_id is null.

Expected Output Table:

employee_idemployee_namedepartmentmanager_name
1Bruce WayneExecutiveExecutive Leadership
2Lucius FoxEngineeringBruce Wayne
3Dick GraysonOperationsBruce Wayne
4Tim DrakeEngineeringLucius Fox
5Barbara GordonAnalyticsLucius Fox

Exercise 11: Peer Salary Discrepancies within Departments (SELF JOIN)

Business Scenario: Compensation analysts are auditing salary equity. Find all instances where an employee earns strictly less than a colleague in the same department who was hired in the exact same calendar year.

Input Table:

employees (sample rows):

employee_idfirst_namedepartmentsalaryhire_date
11AliceEngineering95000.002023-03-15
12BobEngineering110000.002023-08-01
13CharlieEngineering85000.002022-01-10
14DianeAnalytics92000.002023-05-20
15EvanAnalytics98000.002023-11-12

PostgreSQL Solution Query:

sql
SELECT 
    e.employee_id,
    e.first_name AS employee_name,
    e.department,
    e.salary AS employee_salary,
    peer.first_name AS higher_earning_peer,
    peer.salary AS peer_salary,
    peer.salary - e.salary AS salary_difference
FROM employees AS e
INNER JOIN employees AS peer 
    ON e.department = peer.department
   AND EXTRACT(YEAR FROM e.hire_date) = EXTRACT(YEAR FROM peer.hire_date)
   AND e.salary < peer.salary
ORDER BY e.department, salary_difference DESC;

Logic Breakdown:

  • Joins employees to itself using multiple predicates: matching department, matching hire year via EXTRACT(YEAR FROM ...), and a non-equi condition e.salary < peer.salary.
  • Alice and Bob were both hired in 2023 in Engineering; because Alice earns less than Bob, a row is generated showing the $15,000 difference.
  • Charlie was hired in 2022, so he has no peers from his cohort in this dataset.

Expected Output Table:

employee_idemployee_namedepartmentemployee_salaryhigher_earning_peerpeer_salarysalary_difference
11AliceEngineering95000.00Bob110000.0015000.00
14DianeAnalytics92000.00Evan98000.006000.00

Exercise 12: Consecutive Day User Activity Detection (SELF JOIN)

Business Scenario: Gaming analytics tracks user retention streaks. Identify all users who logged into the platform on two consecutive calendar days, without relying on window functions.

Input Table:

user_logins (sample rows):

login_iduser_idlogin_date
15012024-04-10
25012024-04-11
35012024-04-15
45022024-04-10
55022024-04-12

PostgreSQL Solution Query:

sql
SELECT DISTINCT 
    curr.user_id,
    curr.login_date AS first_day,
    next_day.login_date AS consecutive_day
FROM user_logins AS curr
INNER JOIN user_logins AS next_day 
    ON curr.user_id = next_day.user_id
   AND next_day.login_date = curr.login_date + INTERVAL '1 day'
ORDER BY curr.user_id, first_day ASC;

Logic Breakdown:

  • Joins user_logins to itself on matching user_id.
  • The temporal join predicate next_day.login_date = curr.login_date + INTERVAL '1 day' tests for records on the following calendar day.
  • User 501 logged in on both April 10 and 11, satisfying the join condition. User 502 logged in on April 10 and 12 (a two-day gap), so no row is returned.

Expected Output Table:

user_idfirst_dayconsecutive_day
5012024-04-102024-04-11

Exercise 13: Multi-Table Revenue Aggregation without Fan-Out (Advanced CTE Join)

Business Scenario: Executive reporting requires customer order counts alongside their total units purchased. Avoid the join fan-out trap that inflates parent order totals when joining child line-item records.

Input Tables:

customers (sample rows):

customer_idcustomer_name
1001Rachel Green
1002Ross Geller

orders (sample rows):

order_idcustomer_idorder_total
2011001100.00
2021001150.00
203100280.00

order_items (sample rows):

item_idorder_idquantity
12012
22013
32021
42034

PostgreSQL Solution Query:

sql
WITH customer_orders AS (
    SELECT 
        customer_id,
        COUNT(order_id) AS total_orders,
        SUM(order_total) AS true_order_revenue
    FROM orders
    GROUP BY customer_id
),
customer_items AS (
    SELECT 
        o.customer_id,
        SUM(oi.quantity) AS total_units_purchased
    FROM orders AS o
    INNER JOIN order_items AS oi ON o.order_id = oi.order_id
    GROUP BY o.customer_id
)
SELECT 
    c.customer_id,
    c.customer_name,
    COALESCE(co.total_orders, 0) AS total_orders,
    COALESCE(co.true_order_revenue, 0.00) AS true_order_revenue,
    COALESCE(ci.total_units_purchased, 0) AS total_units_purchased
FROM customers AS c
LEFT JOIN customer_orders AS co ON c.customer_id = co.customer_id
LEFT JOIN customer_items AS ci ON c.customer_id = ci.customer_id
ORDER BY true_order_revenue DESC;

Logic Breakdown:

  • In naive queries that join orders directly to order_items, order 201 is duplicated across its two child rows, multiplying order_total from $100.00 to $200.00!
  • Pre-aggregating orders and line items into separate Common Table Expressions (CTEs) before joining them back to the customer preserves data grains and prevents inflation.
  • For a deep dive into this interview pattern, read our dedicated guide on SQL JOIN Fan-Out: Fixing Duplicate Rows & Broken SUM.

Expected Output Table:

customer_idcustomer_nametotal_orderstrue_order_revenuetotal_units_purchased
1001Rachel Green2250.006
1002Ross Geller180.004

Exercise 14: Promotional Pricing Discount Range Matching (Non-Equi BETWEEN Join)

Business Scenario: The marketing operations team launched several targeted discount campaigns. Re-evaluate customer purchases by joining orders to the promotions table to find the applicable discount percentage based on order date and product category.

Input Tables:

orders (sample rows):

order_idcustomer_idcategoryorder_dategross_amount
1701Audio2024-03-15200.00
2702Audio2024-04-10150.00
3703Displays2024-03-20400.00

promotions (sample rows):

promo_idpromo_namecategorystart_dateend_datediscount_pct
P-1Spring Audio FestAudio2024-03-012024-03-3115.00
P-2Spring Display BlowoutDisplays2024-03-152024-03-2510.00

PostgreSQL Solution Query:

sql
SELECT 
    o.order_id,
    o.category,
    o.order_date,
    o.gross_amount,
    COALESCE(p.promo_name, 'No Promotion Applied') AS promotion_applied,
    COALESCE(p.discount_pct, 0.00) AS discount_percentage,
    ROUND(
        o.gross_amount * (1.0 - COALESCE(p.discount_pct, 0.00) / 100.0), 
        2
    ) AS net_discounted_amount
FROM orders AS o
LEFT JOIN promotions AS p 
    ON o.category = p.category
   AND o.order_date BETWEEN p.start_date AND p.end_date
ORDER BY o.order_id ASC;

Logic Breakdown:

  • This is a non-equi join: the join condition uses BETWEEN p.start_date AND p.end_date alongside category equality.
  • Order 1 falls within the Spring Audio Fest window (March 15) and receives a 15% discount.
  • Order 2 is in the Audio category, but was placed in April (after the promo ended), so it receives no discount.
  • COALESCE handles orders without matching promotions by falling back to 0% discount.

Expected Output Table:

order_idcategoryorder_dategross_amountpromotion_applieddiscount_percentagenet_discounted_amount
1Audio2024-03-15200.00Spring Audio Fest15.00170.00
2Audio2024-04-10150.00No Promotion Applied0.00150.00
3Displays2024-03-20400.00Spring Display Blowout10.00360.00

Exercise 15: Zero-Filled Sales Calendar Spine (CROSS JOIN + LEFT JOIN)

Business Scenario: Executive dashboards require a daily sales trend for the launch week of April 1–4, 2024. If a category had zero sales on a given day, it must display $0.00 rather than disappearing from the report.

Input Tables:

calendar_days (generated via PostgreSQL generate_series):

report_date
2024-04-01
2024-04-02
2024-04-03
2024-04-04

categories (sample rows):

category_name
Electronics
Books

orders (sample rows):

order_idcategoryorder_dateamount
10Electronics2024-04-01500.00
11Electronics2024-04-03250.00
12Books2024-04-0140.00

PostgreSQL Solution Query:

sql
WITH date_spine AS (
    SELECT generate_series(
        '2024-04-01'::DATE, 
        '2024-04-04'::DATE, 
        INTERVAL '1 day'
    )::DATE AS report_date
),
category_list AS (
    SELECT DISTINCT category AS category_name FROM orders
),
reporting_matrix AS (
    -- CROSS JOIN builds every date × category combination
    SELECT 
        d.report_date,
        c.category_name
    FROM date_spine AS d
    CROSS JOIN category_list AS c
)
SELECT 
    m.report_date,
    m.category_name,
    COALESCE(SUM(o.amount), 0.00) AS daily_revenue
FROM reporting_matrix AS m
LEFT JOIN orders AS o 
    ON m.report_date = o.order_date
   AND m.category_name = o.category
GROUP BY m.report_date, m.category_name
ORDER BY m.category_name, m.report_date ASC;

Logic Breakdown:

  • Real-world transactional tables omit rows for days with no activity. Joining raw orders directly would skip those dates entirely.
  • A CROSS JOIN between a date spine and a category list generates a baseline grid containing every combination (4 days × 2 categories = 8 rows).
  • A LEFT JOIN from this reporting matrix to orders attaches transaction totals where they exist.
  • COALESCE(SUM(o.amount), 0.00) replaces NULL values with $0.00 for zero-sales days, producing a complete time series ready for visualization.

Expected Output Table:

report_datecategory_namedaily_revenue
2024-04-01Books40.00
2024-04-02Books0.00
2024-04-03Books0.00
2024-04-04Books0.00
2024-04-01Electronics500.00
2024-04-02Electronics0.00
2024-04-03Electronics250.00
2024-04-04Electronics0.00

4 Common SQL Join Interview Traps

During technical interviews, hiring teams evaluate whether you understand edge cases and performance trade-offs:

  1. The Cartesian Explosion (Missing Join Condition): Omitting the ON clause or joining on non-unique keys without sufficient constraints causes a full Cartesian product ($N \times M$ rows), exhausting database memory.
  2. Accidental Inner Joins via WHERE Predicates: As covered in Part 1, filtering nullable right-table columns in the WHERE clause strips out unmatched left rows, defeating the purpose of a LEFT JOIN.
  3. Double-Counting Aggregate Fan-Out: Summing parent amounts across a one-to-many join multiplies values by the number of matching child rows. Always pre-aggregate in CTEs first.
  4. Three-Valued Logic in Anti-Joins: When using NOT IN (SELECT id FROM ...) with subqueries, if the subquery returns a single NULL, the entire predicate evaluates to UNKNOWN and returns zero records! Use NOT EXISTS or LEFT JOIN ... WHERE right.id IS NULL instead.

Master Advanced SQL Joins with Topfolio

True SQL proficiency comes from diagnosing query errors, reviewing query plans, and practicing joins against live relational schemas.

Continue your journey with Topfolio's guided curricula:

Every lesson and practice exercise on Topfolio is 100% free to learn. You can also earn an optional ₹99 verified certificate to showcase your technical SQL capabilities to hiring managers on LinkedIn.

Practice SQL Joins in Your Live Browser Sandbox

Run queries against live PostgreSQL databases with automated verification on Topfolio. Free to learn, optional ₹99 verified certificate.

Start Free SQL Course

Frequently Asked Questions

What is the practical difference between an INNER JOIN and a LEFT JOIN?

An INNER JOIN returns only records where the join key matches in both tables, discarding non-matching rows. A LEFT JOIN returns all records from the left table regardless of matches, populating right-table columns with NULL when no matching key exists.

Why does a LEFT JOIN return duplicate rows when joining two tables?

A LEFT JOIN produces duplicate rows when the right table contains multiple matching records for a single key from the left table (a one-to-many relationship). This is known as join fan-out and can be resolved by pre-aggregating child records before joining.

How do anti-joins work in SQL to find unmatched records?

An anti-join uses a LEFT JOIN combined with a WHERE clause filtering on a non-nullable column from the right table (e.g., WHERE right_table.id IS NULL). This eliminates all matched records and returns only unmatched rows from the left table.

Why should you avoid putting right-table filters in the WHERE clause of a LEFT JOIN?

Placing right-table filter predicates in the WHERE clause instead of the ON clause inadvertently converts the LEFT JOIN into an INNER JOIN, because NULL values generated for unmatched rows fail the WHERE evaluation and get filtered out.

When should you use a FULL OUTER JOIN instead of an INNER or LEFT JOIN?

Use a FULL OUTER JOIN when you need a bidirectional reconciliation between two datasets where records can exist in either table without a corresponding match in the other, such as balancing financial ledgers or auditing warehouse physical inventory against ERP records.

How can I practice SQL joins on real databases for free?

Topfolio provides an interactive browser-based SQL sandbox where you can practice real-world join queries against PostgreSQL databases with instant automated validation. All learning exercises are 100% free, with an optional verified certificate for ₹99.

Anuj Saini

Written by

Anuj SainiFounder & Lead Instructor

Founder at Topfolio with 6+ years in data & analytics across JPMC, Ultrahuman, and high-growth startups. Sat on hiring panels, reviewed 500+ resumes, and writes practical SQL & data guides.