Blinkit / Zepto Data Analyst SQL Interview Questions: 25 Must-Know Queries & Tips

Blinkit / Zepto Data Analyst SQL Interview Questions: Your Ultimate Guide to Cracking the Role

Landing a Data Analyst role at Blinkit or Zepto is no small feat. From crunching sales numbers to segmenting customer behavior, the interview process tests your SQL expertise, analytical thinking, and problem-solving skills. Over the years, as a SQL tutor and freelance consultant, I’ve guided many aspirants to crack such interviews, and today I’m sharing a complete guide with practical SQL questions, tips, and insights.

Table of Contents

• 7-Day Moving Average for Sales
• Total Hours Worked per Employee
• Top N Highest-Grossing Products
• First and Last Transaction per Customer
• Rank Products by Sales per Region
• Second Highest Salary Without LIMIT
• Percentage Contribution per Product
• Find Gaps in Sequential Data
• Month-over-Month Growth per Product
• Customers Who Purchased Every Quarter
• Cumulative Sales vs Average Sales
• Customers with Higher Monthly Purchases
• Products Never Sold
• Top 3 Revenue-Contributing Customers
• Compare Sales Across Time Periods
• Longest Consecutive Purchase Streak
• Rolling Retention Rate Calculation
• Identify Duplicate Rows
• Delete Duplicate Rows Keeping Earliest
• Median Salary per Department
• Products Sold 3 Consecutive Months
• Customer Cohort Analysis
• Total Time Spent by Users
• Products Never Purchased Together
• Time-Series Report Filling Missing Dates

1. Calculate 7-Day Moving Average for Sales

SELECT
    order_date,
    AVG(sales_amount) OVER
    (ORDER BY order_date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW)
    AS moving_avg
FROM sales;

Pro Tip: Use window functions for efficient rolling calculations. Thoda plan ahead, data ko smooth rakho!

2. Total Hours Worked per Employee per Day

SELECT
    employee_id,
    work_date,
    SUM(TIMESTAMPDIFF(HOUR, in_time, out_time)) AS total_hours
FROM attendance
GROUP BY
    employee_id,
    work_date;

Pro Tip: Always handle missing or NULL timestamps. “Kaam ko adhura mat chhodo!”

3. Top N Highest-Grossing Products per Category

SELECT
    category,
    product_id,
    SUM(sales_amount) AS total_sales,
    RANK() OVER
    (PARTITION BY category ORDER BY SUM(sales_amount) DESC)
    AS rank
FROM sales
GROUP BY
    category,
    product_id
HAVING
    rank <= N;

Pro Tip: RANK() handles ties gracefully. Window functions ka magic dekho!

4. First and Last Transaction per Customer

SELECT
    customer_id,
    MIN(transaction_date) AS first_transaction,
    MAX(transaction_date) AS last_transaction
FROM
    orders
WHERE
    transaction_date BETWEEN '2024-01-01' AND '2024-12-31'
GROUP BY
    customer_id;

Pro Tip: Filter by time range before aggregation to save compute time.

5. Rank Products by Sales Within Each Region

SELECT
    region, product_id,
    RANK() OVER
    (PARTITION BY region ORDER BY SUM(sales_amount) DESC)
    AS product_rank
FROM
    sales
GROUP BY
    region, product_id;

Pro Tip: Add tie-breaker columns if needed for deterministic ranking.

6. Second Highest Salary Without LIMIT

SELECT
    MAX(salary) AS second_highest
FROM
    employees

WHERE
    salary <
    (SELECT MAX(salary) FROM employees);

Pro Tip: Subquery approach avoids database-specific LIMIT syntax. “Seedha aur simple, no jugad!”

7. Percentage Contribution of Each Product

SELECT
    category, product_id,
    SUM(sales_amount)/SUM(SUM(sales_amount)) OVER(PARTITION BY category)*100
    AS pct_contribution
FROM sales
GROUP BY category, product_id;

Pro Tip: Window functions simplify relative metrics calculation.

8. Find Gaps in Sequential Data

SELECT
    id + 1 AS missing_id
FROM invoices i
WHERE NOT EXISTS (SELECT 1 FROM invoices WHERE id = i.id + 1);

Pro Tip: Gap detection ensures audit integrity. “Chhoti chhoti cheezon ka dhyan rakho.”

9. Month-over-Month Growth per Product

SELECT
    product_id,
    MONTH(order_date) AS month,
    SUM(sales_amount) AS monthly_sales,

    (SUM(sales_amount) - LAG(SUM(sales_amount)) OVER

    (PARTITION BY product_id ORDER BY MONTH(order_date))) / LAG(SUM(sales_amount))

    OVER(PARTITION BY product_id ORDER BY MONTH(order_date)) * 100

    AS mom_growth

FROM sales

GROUP BY product_id, MONTH(order_date);

Pro Tip: Handle divide-by-zero errors when last month’s sales are zero.

10. Customers Who Purchased Every Quarter

SELECT customer_id
FROM sales

GROUP BY customer_id

HAVING
    COUNT(DISTINCT QUARTER(order_date)) = 4;

Pro Tip: “Har quarter ka hisaab rakho, tabhi picture clear hogi.”

11. Cumulative Sales vs Average Sales

SELECT

    product_id,

    SUM(sales_amount) OVER

    (PARTITION BY product_id ORDER BY order_date)

    AS cumulative_sales,

    AVG(sales_amount) OVER

    (PARTITION BY product_id)

    AS avg_sales

FROM sales;

Pro Tip: Compare cumulative and average trends for forecasting insights.

12. Customers with Higher Purchases This Month

SELECT

    customer_id

FROM
    sales

WHERE
    MONTH(order_date) = MONTH(CURRENT_DATE)

GROUP BY
    customer_id

HAVING
    SUM(sales_amount) >

    (SELECT

        SUM(sales_amount)

    FROM sales

    WHERE

        MONTH(order_date) = MONTH(CURRENT_DATE - INTERVAL 1 MONTH)

    AND

        customer_id = sales.customer_id);

Pro Tip: Window functions can simplify monthly comparisons.

13. Products Never Sold

SELECT p.product_id, p.category
FROM
    products p
LEFT JOIN
    sales s
ON
    p.product_id = s.product_id
WHERE
    s.product_id IS NULL;

Pro Tip: LEFT JOIN + IS NULL is your go-to for unsold items.

14. Top 3 Customers by Revenue Last Year

SELECT customer_id, SUM(sales_amount) AS total_revenue
FROM sales
WHERE
    YEAR(order_date) = YEAR(CURRENT_DATE - INTERVAL 1 YEAR)
GROUP BY
    customer_id
ORDER BY
    total_revenue DESC
LIMIT 3;

Pro Tip: Always filter by exact year to avoid including partial data.

15. Compare Sales Across Two Time Periods

SELECT
    product_id,
    SUM(CASE
        WHEN order_date BETWEEN '2024-01-01' AND '2024-03-31'
        THEN sales_amount END) AS period1,
    SUM(CASE
        WHEN order_date BETWEEN '2024-04-01' AND '2024-06-30'
        THEN sales_amount END) AS period2
FROM
    sales 

GROUP BY 

    product_id;

Pro Tip: Conditional aggregation helps track trends across periods.

16. Longest Consecutive Purchase Streak per Customer

-- Use window functions to calculate streaks

Pro Tip: Lag/Lead and date difference calculations work wonders. “Chhoti chhoti jeet ka hisaab rakho.”

17. Rolling Retention Rate for App Users

-- Use cohort analysis and window functions

Pro Tip: Useful for product analytics and app growth strategies.

18. Identify Duplicate Rows

SELECT *, COUNT(*) AS cnt
FROM table_name
GROUP BY col1, col2, col3
HAVING COUNT(*) > 1;

19. Delete Duplicate Rows Keeping Earliest

DELETE
FROM
    table_name
WHERE
    id NOT IN
    ( SELECT MIN(id) FROM table_name GROUP BY col1, col2, col3 );

20. Median Salary per Department

SELECT
    department_id,
    PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY salary)
    AS median_salary
FROM
    employees
GROUP BY
    department_id;

21. Products Sold 3 Consecutive Months But Not 4th

-- Use window functions and conditional checks for consecutive months

22. Customer Cohort Analysis

-- Group by first purchase month, calculate retention

23. Total Time Spent by Users Excluding Overlaps

-- Use LEAD/LAG to handle overlapping intervals

24. Products Never Purchased Together

-- Use CROSS JOIN minus existing combinations

25. Time-Series Report Filling Missing Dates

-- Generate a date series and LEFT JOIN with sales to fill zeros

Pro Tip: Preparing for these SQL questions isn’t just about memorizing queries; it’s about understanding data patterns and thinking like a data analyst. Agar aap practice karo, toh interview ki tension apne aap kam ho jayegi!

As a SQL tutor and freelance consultant, I’ve seen that candidates who focus on real-world application rather than rote learning always stand out. Whether you want tutoring, freelance projects, or full-time opportunities, mastering these SQL concepts will make you the go-to professional.