← Shopify Interview Insights

Shopify·Data Analyst·Technical Phone Screen·Intermediate

Intermediate
Apr 2026

Summary

Shopify data/analytics interview with a SQL question that had a sneaky schema trap. Nothing crazy but the shops table detail would've wrecked me if I hadn't noticed it.

Questions Asked (1)

Q1

Write a SQL query that returns the count of distinct user sessions per shop and per day. The sessions table has one row per session, users maps each user to a shop, and the shops table may contain duplicate shop_id rows.

Data ModelingTechnical Trade-offs
Author's notes

The duplicate shop_id thing in the shops table is what gets people.

Create a free account to read the full note

AI HintsAI Generated

Suggested Approach

Start by clarifying the grain of each table and the definition of a session. Then write a query that joins sessions to users to shops, handling duplicate shop_id rows by deduplicating shops first, and finally group by shop and date to count distinct sessions.

Pro tip: Mention that you would deduplicate the shops table using a subquery or DISTINCT before joining to avoid fan-out, and explicitly state your assumption about what constitutes a session (e.g., session_id uniqueness).

1. Clarify table grains and definitions

Confirm that sessions has one row per session, users maps user to shop, and shops may have duplicate shop_id rows. Define what a 'session' is (e.g., session_id) and what 'per day' means (e.g., session start date).

2. Deduplicate shops table

Since shops may contain duplicate shop_id rows, use a subquery with DISTINCT or GROUP BY to get a unique list of shop_ids before joining, preventing row multiplication.

3. Join sessions to users and shops

Join sessions to users on user_id, then to the deduplicated shops on shop_id. Ensure the join keys are correct and consider the direction of the relationship.

4. Group and count distinct sessions

Group by shop_id (or shop name) and the date extracted from the session timestamp. Use COUNT(DISTINCT session_id) to count unique sessions per group.

5. Validate and discuss trade-offs

Check for edge cases like sessions with no user, users with no shop, or null dates. Discuss performance implications of deduplication and distinct counts on large datasets.

Key Points to Mention

  • Deduplicating the shops table to avoid fan-out from duplicate shop_id rows.
  • Using COUNT(DISTINCT session_id) to ensure unique sessions are counted.
  • Grouping by both shop identifier and date (e.g., DATE(session_start)).
  • Handling potential NULLs or orphaned records in joins (e.g., sessions without users).
  • Considering performance: deduplication and distinct counts can be expensive; mention indexing or pre-aggregation.
  • Clarifying assumptions about session definition and time zone for 'per day'.

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