← Intuit Interview InsightsSoftware Engineer

Intuit·Software Engineer·Technical Phone Screen·Intermediate

Intermediate
Jun 2026

Summary

SQL-heavy technical screen for a software engineer role at Intuit. One question, pretty focused, they wanted to see if you knew conditional aggregation and could handle edge cases like coins with zero transactions.

Questions Asked (1)

Q1

Given a coins table and a transactions table, write a single SQL query that returns each coin's name alongside the summed transaction amount per calendar quarter, the total transaction count, and the total amount. Handle coins that have no transactions.

Data ModelingTechnical Trade-offs
Author's notes

The quarterly breakdown part is straightforward if you've seen conditional aggregation before.

Create a free account to read the full note

AI HintsAI Generated

Suggested Approach

Start by clarifying the schema and expected output format, then outline a query that joins coins to transactions with a LEFT JOIN to include coins with no transactions. Use conditional aggregation to compute quarterly sums, total count, and total amount, ensuring correct handling of NULLs and grouping by coin and quarter.

Pro tip: Mention that you would verify the quarter extraction logic with the interviewer, as different SQL dialects handle date functions differently, and explicitly state how you'd handle coins with no transactions (e.g., using COALESCE to return 0 instead of NULL).

1. Clarify requirements and schema

Ask about the table structures, data types, and expected output format (e.g., one row per coin per quarter or pivoted columns). Confirm how to handle coins with no transactions and whether quarters should be based on calendar year or fiscal year.

2. Design the join and grouping

Use a LEFT JOIN from coins to transactions to include all coins. Group by coin name and quarter (extracted from transaction date) to compute per-quarter sums, and also compute overall totals using window functions or a separate aggregation.

3. Write conditional aggregation for quarterly sums

Use CASE expressions inside SUM to pivot quarterly amounts into columns, or use GROUP BY with quarter extraction if the output should be one row per coin per quarter. Ensure NULLs are handled with COALESCE.

4. Compute total count and total amount

Use COUNT(transaction_id) and SUM(amount) over the entire dataset per coin, either via window functions or by joining to a subquery that aggregates totals per coin.

5. Validate and optimize

Check for edge cases (e.g., coins with no transactions, null amounts) and consider indexing on join keys. Discuss potential performance implications and alternative approaches like using CTEs for readability.

Key Points to Mention

  • Use of LEFT JOIN to include coins with no transactions
  • Handling NULLs with COALESCE or IFNULL to return 0 for sums and counts
  • Quarter extraction using date functions (e.g., EXTRACT(QUARTER FROM date) or DATEPART)
  • Conditional aggregation with CASE statements for pivoting quarterly sums
  • Window functions (e.g., SUM() OVER (PARTITION BY coin_id)) for total amounts without collapsing rows
  • Performance considerations: indexing on foreign keys and date columns, and avoiding unnecessary subqueries

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