Intuit·Data Scientist·Technical Phone Screen·Senior

Senior

Apr 2026

Summary

Intuit Data Scientist interview with a pretty gnarly SQL question that built on a previous problem. The whole thing was a follow-up to an earlier monthly subscriber query and they wanted you to layer in an anti-join to exclude free-tier companies, plus explain the performance trade-offs of different approaches under columnar engines.

Questions Asked (1)

Take your existing query for monthly new subscribers and year-over-year metrics, then modify it to exclude any company that ever had a free subscription. The exclusion must happen before aggregation, guard against duplicate IDs in the free subs table, and must not accidentally re-include rows through join behavior. Also explain the trade-offs between NOT EXISTS and LEFT JOIN ... IS NULL for this kind of anti-join on a columnar OLAP engine.

Technical Trade-offsData ModelingProduct Analytics & Metrics

Author's notes

This one stacked a lot of constraints on top of each other and I kept second-guessing the order of operations.

Create a free account to read the full note

AI HintsAI Generated

Suggested Approach

First, restate the goal and clarify the schema: identify the free subscriptions table and the main subscriptions table, then design an anti-join that filters out any company with a free subscription before aggregation. Write the modified query using NOT EXISTS or LEFT JOIN ... IS NULL, ensuring deduplication of free subscription IDs and correct join keys. Finally, discuss the trade-offs between the two anti-join methods on a columnar OLAP engine, focusing on performance, readability, and null-handling semantics.

Pro tip: Mention that on columnar OLAP engines like Snowflake or BigQuery, NOT EXISTS is often optimized into an anti-join and can be more efficient than LEFT JOIN ... IS NULL, especially when the free subs table is large, because it avoids materializing and filtering nulls. Also, explicitly deduplicate the free subs table with DISTINCT or GROUP BY to prevent accidental row multiplication.

1. Clarify the schema and requirements

Identify the main subscriptions table (with monthly new subscribers and YoY metrics) and the free subscriptions table. Confirm the join key (e.g., company_id) and ensure the exclusion is based on any historical free subscription.

2. Deduplicate the free subs table

Use a subquery with DISTINCT or GROUP BY on company_id to guard against duplicate IDs in the free subs table, preventing row multiplication in the anti-join.

3. Apply the anti-join before aggregation

Filter out companies that ever had a free subscription using either NOT EXISTS or LEFT JOIN ... IS NULL. Ensure the anti-join is applied before any aggregation to avoid re-inclusion of rows.

4. Write the modified query

Construct the full query with the anti-join, then compute monthly new subscribers and YoY metrics. Verify that the join behavior does not accidentally re-include rows (e.g., due to nulls or duplicates).

5. Discuss trade-offs

Compare NOT EXISTS and LEFT JOIN ... IS NULL on a columnar OLAP engine: performance (anti-join optimization vs. null filtering), readability, and correctness with nulls. Mention that NOT EXISTS is generally preferred for anti-joins.

Key Points to Mention

Anti-join semantics: excluding rows based on the absence of matching records in another table.
Deduplication of the free subs table using DISTINCT or GROUP BY to avoid row multiplication.
Order of operations: anti-join must occur before aggregation to prevent re-inclusion.
NOT EXISTS vs. LEFT JOIN ... IS NULL: performance on columnar OLAP engines, null-handling, and readability.
Potential pitfalls: nulls in join keys, duplicate IDs, and join behavior that might re-include rows.
Impact on YoY metrics: ensuring the exclusion is consistent across all time periods.

AI-generated suggestions, not part of the candidate's original notes. May be inaccurate — verify before relying on them.