PayPal·Data Scientist·Technical Phone Screen·Intermediate

Intermediate

Jun 2026

Summary

SQL-heavy technical screen for a Data Scientist role at PayPal. The questions were all about conditional aggregation and filtering, which sounds basic until you're being asked to explain the difference out loud under pressure.

Questions Asked (3)

Write a SQL query to compute the total amount for rows meeting a specific condition. Compare using SUM with a WHERE filter versus SUM(CASE WHEN ... THEN ... END) for conditional aggregation.

Technical Trade-offsData ModelingProduct Analytics & Metrics

Author's notes

I knew both approaches but fumbled explaining when you'd actually prefer one over the other.

Create a free account to read the full note

AI HintsAI Generated

Suggested Approach

Start by clarifying the business question and the data model, then write both queries and compare their performance and readability. Highlight that SUM with WHERE filters rows before aggregation, while SUM(CASE WHEN) conditionally aggregates within a single pass, making it more flexible for multiple conditions.

Pro tip: In interviews, always mention that SUM(CASE WHEN) can compute multiple conditional aggregates in one table scan, which is often more efficient than multiple queries with WHERE. Also, note that NULL handling and data types can affect results, so test with edge cases.

1. Clarify the requirement

Restate the question to ensure you understand the condition and the desired output. Ask if the condition is static or dynamic, and whether multiple conditions might be needed.

2. Write the WHERE version

Construct a query using SUM with a WHERE clause to filter rows before aggregation. Explain that this approach is straightforward and often more readable for a single condition.

3. Write the CASE WHEN version

Construct a query using SUM(CASE WHEN condition THEN value ELSE 0 END). Explain that this allows conditional aggregation without filtering out rows, enabling multiple aggregates in one query.

4. Compare trade-offs

Discuss performance (e.g., index usage, number of scans), readability, and flexibility. Mention that WHERE may be faster for a single condition, while CASE WHEN is better for multiple conditions or when you need other aggregates from the same rows.

5. Recommend based on context

Conclude with a recommendation tailored to the scenario, such as using WHERE for simple filters and CASE WHEN for complex reporting needs. Emphasize testing and understanding the data distribution.

Key Points to Mention

SUM with WHERE filters rows before aggregation, reducing the number of rows processed.
SUM(CASE WHEN) evaluates the condition for each row and aggregates conditionally, allowing multiple conditional sums in one query.
Performance: WHERE can leverage indexes and may be faster for a single condition; CASE WHEN may require a full scan but avoids multiple passes.
Readability: WHERE is often clearer for simple conditions; CASE WHEN can become verbose but is more flexible.
NULL handling: SUM ignores NULLs, but CASE WHEN without ELSE returns NULL for non-matching rows, which SUM ignores, effectively treating them as 0.
Use cases: WHERE for ad-hoc filtering; CASE WHEN for pivot-like reports or when combining multiple metrics.

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

Rewrite a SUM with WHERE filter query using CASE WHEN for conditional aggregation instead.

Data ModelingTechnical Trade-offs

Author's notes

Pretty mechanical once you understand the concept.

Create a free account to read the full note

AI HintsAI Generated

Suggested Approach

Start by clarifying the original query's intent and the desired output, then demonstrate the rewrite by moving the WHERE condition into a CASE expression inside the SUM. Explain the trade-offs between filtering before aggregation (WHERE) and conditional aggregation (CASE WHEN), and when each is preferable.

Pro tip: Mention that conditional aggregation can be more efficient when you need multiple filtered aggregates in one pass, but WHERE filtering reduces the number of rows processed and is often faster for a single condition. Also, note that CASE WHEN can be used with other aggregates like COUNT and AVG.

1. Understand the original query

Identify the table, the SUM column, and the WHERE condition. Confirm the expected result set and any grouping.

2. Rewrite using CASE WHEN

Replace the WHERE filter with a CASE expression inside the SUM: SUM(CASE WHEN condition THEN column ELSE 0 END). Ensure the ELSE 0 handles non-matching rows.

3. Compare performance and semantics

Discuss how the two approaches differ in execution: WHERE filters rows before aggregation, while CASE WHEN processes all rows but only sums matching ones. Consider index usage and data volume.

4. Discuss trade-offs and use cases

Explain scenarios where each is better: WHERE for single filtered aggregates, CASE WHEN for multiple conditional aggregates in one query or when the filter is complex.

5. Provide a concrete example

Write a sample SQL query showing both versions side-by-side, and optionally mention how to extend to multiple conditions.

Key Points to Mention

The basic syntax: SUM(CASE WHEN condition THEN column ELSE 0 END)
Handling NULLs: CASE WHEN can avoid NULLs by using ELSE 0, whereas SUM ignores NULLs
Performance implications: WHERE reduces rows early, CASE WHEN may scan more rows but allows multiple aggregates
Use cases: single vs multiple conditional aggregates, dynamic filtering
Readability and maintainability: WHERE is simpler for single conditions, CASE WHEN can be more complex but flexible
Compatibility: CASE WHEN is standard SQL and works across databases

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

In a grouped SQL query, can you place an aggregation condition in the HAVING clause? Does this syntax work in MySQL specifically, and why?

Data ModelingTechnical Trade-offsAlgorithms & Data Structures

Author's notes

This tripped me up a bit.

Create a free account to read the full note

AI HintsAI Generated

Suggested Approach

Start by confirming that yes, aggregation conditions can be placed in the HAVING clause, and explain that HAVING is specifically designed to filter after grouping and aggregation. Then clarify that MySQL supports this syntax, but note that MySQL also allows HAVING to reference non-aggregated columns (an extension), which can lead to non-standard behavior. Emphasize the logical order of SQL operations to justify why HAVING is the correct place for such conditions.

Pro tip: Mention that while MySQL permits aggregation conditions in HAVING, using it for non-aggregated columns can cause confusion and is not portable to other databases. Also, highlight that filtering before grouping with WHERE improves performance by reducing the number of rows aggregated.

1. Confirm the syntax

State clearly that yes, you can place an aggregation condition in the HAVING clause, and provide a simple example like HAVING COUNT(*) > 5.

2. Explain the logical order

Describe the SQL logical processing order: FROM, WHERE, GROUP BY, HAVING, SELECT, ORDER BY. This shows why HAVING is evaluated after grouping and can use aggregate functions.

3. Address MySQL support

Confirm that MySQL supports aggregation conditions in HAVING. Optionally note that MySQL also allows HAVING to reference columns not in GROUP BY, which is non-standard.

4. Discuss trade-offs and best practices

Mention that HAVING should be used for aggregate filters, while WHERE should be used for row-level filters to optimize performance. Avoid using HAVING for non-aggregate conditions when possible.

5. Provide a concrete example

Give a short SQL example, such as SELECT department, AVG(salary) FROM employees GROUP BY department HAVING AVG(salary) > 50000; to illustrate the concept.

Key Points to Mention

HAVING clause is used to filter groups after aggregation, while WHERE filters rows before grouping.
Aggregate functions like COUNT, SUM, AVG can be used in HAVING conditions.
MySQL supports standard HAVING with aggregates, and also allows non-aggregated columns in HAVING (a MySQL extension).
The logical order of SQL query execution: FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY.
Using WHERE to filter rows before grouping can improve performance by reducing the data processed in aggregation.
Portability: relying on MySQL's non-standard HAVING behavior may cause issues when migrating to other databases.

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