Top SQL Interview Questions for Senior Data Analyst Roles

Are you preparing for a Senior Data Analyst or SQL-based interview in top tech companies? Whether you’re applying for well-known firms in the US or global MNCs, the following SQL technical interview questions will give you hands-on insight into real-world queries asked during high-level analyst interviews.

This post is based on an actual candidate’s experience and includes self-joins, window functions, CTEs, and performance tuning — all written with practical examples and interview-focused explanations.

🧠 Easy SQL Questions

1. Generate a report that shows employees who their manager is (SELF JOIN)

This is a classic SQL interview question to test your understanding of self-joins and hierarchical relationships within the same table.

✅ SQL Query:

SELECT 
    e.employee_id AS EmployeeID,
    e.employee_name AS EmployeeName,
    m.employee_name AS ManagerName
FROM 
    employees e
LEFT JOIN 
    employees m 
    ON e.manager_id = m.employee_id;

🔍 Explanation:

employees table is joined with itself.
e represents the employee, and m represents the manager.
A LEFT JOIN ensures employees without a manager (like a CEO) are still shown.

SQL Self Join Example
Show employee and manager using SQL
SQL employee manager relationship

2. Show the latest used product (using MAX on datetime)

This tests your basic aggregation skills and filtering.

✅ SQL Query:

SELECT TOP 1 *
FROM product_usage
ORDER BY usage_datetime DESC;

Or using MAX:

SELECT *
FROM product_usage
WHERE usage_datetime = (SELECT MAX(usage_datetime) FROM product_usage);

🔍 Explanation:

TOP 1 with ORDER BY DESC is the most efficient.
The second query uses a subquery with MAX() to get the latest timestamp.

⚙️ Medium Difficulty SQL Questions

3. Find customers with the highest orders between a date span

✅ SQL Query:

WITH order_summary AS (
    SELECT 
        customer_id,
        COUNT(order_id) AS total_orders
    FROM 
        orders
    WHERE 
        order_date BETWEEN '2023-01-01' AND '2023-12-31'
    GROUP BY 
        customer_id
)
SELECT *
FROM order_summary
WHERE total_orders = (
    SELECT MAX(total_orders) FROM order_summary
);

🔍 Explanation:

Uses a CTE to count orders per customer.
Filters within a date range using BETWEEN.
Finds customer(s) with the maximum order count.

SQL highest orders by customer
SQL count orders by date
SQL top customer query

4. Calculate change over time of products for a date span

This tests window functions and time-based analysis.

✅ SQL Query:

WITH product_sales AS (
    SELECT 
        product_id,
        DATE(order_date) AS sale_date,
        SUM(sales_amount) AS daily_sales
    FROM 
        sales
    WHERE 
        order_date BETWEEN '2024-01-01' AND '2024-12-31'
    GROUP BY 
        product_id, DATE(order_date)
),
sales_change AS (
    SELECT 
        product_id,
        sale_date,
        daily_sales,
        LAG(daily_sales) OVER (PARTITION BY product_id ORDER BY sale_date) AS previous_day_sales,
        (daily_sales - LAG(daily_sales) OVER (PARTITION BY product_id ORDER BY sale_date)) AS change_in_sales
    FROM 
        product_sales
)
SELECT * FROM sales_change;

🔍 Explanation:

LAG() gets the previous day’s sales.
Calculates day-over-day sales change per product.
Uses CTEs, aggregation, and window functions.

🔥 Hard SQL Questions

5. Find users who were active for 4 consecutive days

This is an advanced query involving date logic and dense ranking.

✅ SQL Query:

WITH activity_dates AS (
    SELECT 
        user_id,
        DATE(activity_time) AS activity_date
    FROM 
        user_activity
    GROUP BY 
        user_id, DATE(activity_time)
),
numbered_activity AS (
    SELECT 
        user_id,
        activity_date,
        ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY activity_date) -
        DENSE_RANK() OVER (PARTITION BY user_id ORDER BY activity_date) AS date_group
    FROM 
        activity_dates
),
streaks AS (
    SELECT 
        user_id,
        COUNT(*) AS consecutive_days
    FROM 
        numbered_activity
    GROUP BY 
        user_id, date_group
    HAVING 
        COUNT(*) >= 4
)
SELECT DISTINCT user_id FROM streaks;

🔍 Explanation:

The idea is to subtract row number and rank to identify consecutive sequences.
Groups users by sequence and filters those with 4+ consecutive days.

SQL consecutive login days
SQL streak calculation
SQL detect user activity sequence

📊 Other Technical Questions (Non-Code)

6. How do you handle slow-running queries?

✅ Key Talking Points:

Use EXPLAIN PLAN or Query Execution Plans to identify bottlenecks.
Add indexes on frequently filtered columns.
Avoid **SELECT *** — use only required columns.
Use CTEs and subqueries efficiently.
Archive or partition large datasets.

7. What do you know about indexing and big data?

✅ Answer Summary:

Indexes improve read performance but slow down writes.
Use clustered and non-clustered indexes as per query patterns.
For big data: consider distributed databases (e.g., Hive, Presto, Snowflake).
Optimize with partitioning, bucketing, and caching layers.

8. How do you ensure results are correct in large datasets?

✅ Strategies:

Use row counts, checksums, or sample queries to validate.
Compare against known metrics or legacy systems.
Create data validation scripts or dashboards.

9. Do you have experience with Tableau or other BI tools?

✅ Tips:

Share dashboards you’ve built.
Explain how you combined SQL + Tableau for insights.
Mention use of parameters, LOD expressions, and custom calculations.

✅ Conclusion

SQL interviews — especially at senior levels — aren’t just about writing queries. They’re about solving real data problems at scale, demonstrating performance tuning awareness, and showing business understanding via visualizations. For a deeper dive into SQL performance optimization techniques, check out this guide from SQLShack that covers indexing, execution plans, and more.

🔗 Want more interview questions and ready-to-use SQL examples? Explore our SQL Interview Q&A Hub for detailed guides and downloadable scripts.