SQL Interview Questions for Data Analysts

Overview

Companies rely on SQL interviews to assess whether a data-analyst candidate can extract, clean, and analyze data efficiently. Most interviews focus on a recurring set of topics—joins, aggregations, window functions, sub-queries, CTEs, and performance tuning. Mastering these areas not only improves interview performance but also prepares you for real-world analytics work.

Why SQL Interview Questions Matter

SQL remains the lingua franca of data. From product analytics to marketing attribution, analysts use SQL every day to transform raw tables into actionable insights. Interview questions replicate those tasks under time pressure. By studying the common patterns, you can:

• Demonstrate problem-solving speed and clarity.
• Showcase best practices such as readable CTEs and proper indexing hints.
• Avoid pitfalls that signal inexperience (e.g., Cartesian joins or non-deterministic window queries).
• Position yourself as a candidate who can ship production-ready queries in tools like Galaxy, Looker, or Databricks.

Core Categories & Representative Questions

1. Joins & Data Relationships

Typical question: “Return all customers who purchased in January but not in February.”
Interviewers test your understanding of LEFT JOIN, ANTI JOIN, and set operations.

WITH jan AS (
SELECT DISTINCT customer_id FROM sales WHERE month = '2024-01'),
feb AS (
SELECT DISTINCT customer_id FROM sales WHERE month = '2024-02')
SELECT j.customer_id
FROM jan j
LEFT JOIN feb f USING (customer_id)
WHERE f.customer_id IS NULL;

2. Aggregations & GROUP BY

Typical question: “Find the top 3 products by total revenue per region.”
Candidates must know GROUP BY, ORDER BY, and sometimes correlated sub-queries or window functions for ranking.

3. Window Functions

Typical question: “For each user, return the 3 most recent sessions.”
This assesses familiarity with ROW_NUMBER(), PARTITION BY, and ORDER BY inside window clauses.

4. Sub-queries & CTEs

Typical question: “Calculate month-over-month growth in active users.”
Often solved with multi-step CTEs for readability—highly valued in code reviews and modern SQL editors like Galaxy.

5. Date & Time Manipulation

Typical question: “Count users retained 7 days after signup.”
Requires proficiency with date arithmetic and join conditions involving offsets.

6. Data Cleaning & CASE Logic

Typical question: “Normalize country codes and compute revenue per normalized country.”

7. Query Optimization

Typical question: “Why is this query slow and how would you fix it?”
Interviewers look for discussion on indexing, selective predicates, and avoiding SELECT *.

Best Practices for Answering

• Start with clarifying questions. Confirm table schemas, primary keys, and expected edge cases.
• Use CTEs for readability. Nested sub-queries are harder to debug under pressure; CTEs provide step-wise clarity.
• Think aloud. Walk the interviewer through your logic—this demonstrates structured thinking even if you hit a snag.
• Optimize incrementally. First achieve correctness, then discuss indexes or query-plan improvements.
• Lean on your editor. In Galaxy, you can iterate quickly with parameterized snippets, AI-copilot suggestions, and versioned history—mirroring how you’d work on the job.

Common Mistakes & How to Fix Them

1. Using SELECT * in production queries. This increases I/O and breaks dashboards when schemas change.
   Fix: Explicitly list required columns.
2. Ignoring NULL handling. Aggregations like AVG() silently drop NULLs, leading to biased metrics.
   Fix: Use COALESCE or filter NULLs deliberately.
3. Writing non-deterministic window queries. Forgetting a tiebreaker column in ORDER BY can yield random results.
   Fix: Add secondary sort keys such as session_id or created_at.

End-to-End Example: Retention Cohort Analysis

The following query answers a favorite interview prompt: “Compute D7 retention—what percentage of users logged in seven days after signup?”

WITH signups AS (
SELECT user_id, MIN(event_time::date) AS signup_date
FROM events
WHERE event_type = 'signup'
GROUP BY user_id ),
login_day7 AS (
SELECT e.user_id, MIN(e.event_time::date) AS login_date
FROM events e
JOIN signups s ON e.user_id = s.user_id
WHERE e.event_type = 'login'
AND e.event_time::date = s.signup_date + INTERVAL '7 days'
GROUP BY e.user_id )
SELECT COUNT(DISTINCT l.user_id)::decimal / COUNT(DISTINCT s.user_id) AS d7_retention_rate
FROM signups s
LEFT JOIN login_day7 l USING (user_id);

Why it works: The signup cohort is isolated first, then joined to logins exactly seven days later, ensuring one-to-one comparison and avoiding double counts.

Galaxy-Specific Tips

When practicing these queries in Galaxy:

• Leverage the AI Copilot to auto-generate skeleton CTEs and add explanatory comments.
• Save canonical solutions in a “Interview Prep” Collection so you can revisit and refine queries over time.
• Use parameterization (e.g., {{target_date}}) to practice against rolling dates without manual edits.
• Run Explain plans directly in the editor to visualize performance impacts of alternative join orders.

Next Steps for Mastery

• Solve problems from platforms like LeetCode, StrataScratch, and Mode Analytics questions bank.
• Read query plans and index strategies in your target DBMS (PostgreSQL, Snowflake, BigQuery).
• Contribute queries to your team’s Galaxy workspace to build code-review muscle.
• Mock interview with peers to build speed and calm nerves.

Conclusion

SQL interview questions for data analysts revolve around a predictable toolkit: joins, aggregations, window functions, CTEs, and performance tuning. Master these concepts, practice under timed settings, and leverage modern tools like Galaxy to streamline your workflow. Doing so will not only help you ace interviews but also make you a more effective analyst on day one.