75 Ecommerce and Marketing Analytics Interview Questions (With Model SQL and Answers)

This collection of 75 ecommerce and marketing analytics interview questions works through what interviewers actually ask, from business fundamentals to advanced SQL, statistics and behavioral rounds, each with a model answer you can study or adapt.

Most of these interviews are not really testing SQL syntax. They are testing whether you can turn a business question into a query, then explain what the number means and what someone should do about it. Every SQL question below uses one shared practice schema so you can follow the logic from one question to the next instead of relearning a new dataset each time.

How to use these ecommerce and marketing analytics interview questions

Each question below follows the same shape. First the question, close to how it might actually be asked. Then a short answer that explains the approach in plain language, because most interviewers care more about how you think than about perfect syntax. Where SQL applies, a model query follows, written against one shared practice schema described in the next section, so the tables and columns stay consistent from question 1 to question 75.

A few honest notes before you start:

• These are not real questions pulled from any named company. They are written the way ecommerce and marketing analytics interviews are actually structured, based on common patterns across business analyst, data analyst and marketing analytics roles.
• The SQL below follows standard ANSI SQL, with a few PostgreSQL style functions (DATE_TRUNC, generate_series) called out where used. If the company you are interviewing with uses BigQuery, Snowflake or MySQL, the underlying logic carries over even when a function name changes.
• Reading a question and immediately reading the model answer will not build the skill on its own. Try writing your own query first, even a rough one, before checking how it compares below.
• Interviewers often care as much about the sentence after the query as the query itself. Wherever it adds something real, each answer closes with what the result would actually tell a business stakeholder.

The practice schema

Every SQL question in this guide is written against the same seven tables. You will not need all of them for every query, but knowing the shape up front makes it much easier to reason about joins as the questions get harder.

A couple of assumptions worth stating, because a real interview schema would come with the same kind of fine print. An order can contain multiple products, so revenue questions almost always need order_items rather than orders alone. The sessions table logs every visit whether or not it converted, which is what makes funnel and conversion rate questions possible. And acquisition_channel on customers records how someone first arrived, while campaign_id on sessions and email_events tracks activity after that, which matters for a few of the attribution questions later on.

Section A: Ecommerce fundamentals and business metrics

Q1. What is the difference between gross merchandise value (GMV) and revenue, and why does the distinction matter?

GMV is the total value of everything sold before any deductions such as returns, cancellations or discounts. Revenue is what the business actually recognizes after those deductions. A marketplace often leads with GMV because it reflects overall platform activity, but an internal analytics team almost always works from net revenue because that is what flows into margin and profitability. What this tells the business: if GMV is growing faster than revenue, returns or heavy discounting are quietly eating into that growth, and that gap is worth investigating before celebrating the top line number.

Q2. How would you calculate average order value (AOV), and what can make it misleading on its own?

AOV is total revenue divided by number of orders in a period.

SELECT
  ROUND(SUM(oi.quantity * oi.unit_price) / COUNT(DISTINCT o.order_id), 2) AS avg_order_value
FROM orders o
JOIN order_items oi ON oi.order_id = o.order_id
WHERE o.status = 'completed';

AOV can rise for reasons that have nothing to do with a healthier business. If fewer customers are ordering but each order is larger, AOV climbs while total revenue and customer count could both be falling. That is why AOV is almost always read next to order volume and total revenue rather than reported alone.

Q3. How would you calculate conversion rate from the sessions and orders data, and what does a low number actually tell you?

Conversion rate is the share of sessions that resulted in a purchase.

SELECT
  ROUND(100.0 * SUM(CASE WHEN converted_flag = 1 THEN 1 ELSE 0 END) / COUNT(*), 2) AS conversion_rate_pct
FROM sessions;

A low conversion rate on its own does not tell you where the problem lives. It could mean the traffic is low intent, the pricing is off, the site is slow, or checkout is broken for a specific device. That is why conversion rate is usually the starting question in an interview, not the ending one, and a strong answer says what you would look at next: conversion by channel, by device, and by new versus returning customer.

Q4. What is the difference between gross margin and contribution margin in an ecommerce context?

Gross margin is revenue minus the cost of goods sold, expressed as a percentage of revenue. Contribution margin goes a step further and also subtracts variable costs directly tied to the sale, things like payment processing fees, shipping, and return handling. Two products can have identical gross margin and very different contribution margin if one is expensive to ship or gets returned far more often. A business deciding where to spend marketing dollars should generally look at contribution margin, because that is what is actually left over after the sale happens, not just after the product itself is paid for.

Q5. How is customer acquisition cost (CAC) calculated, and why is marketing spend divided by new customers not always the full picture?

The basic formula is total marketing spend divided by the number of new customers acquired in that period. The complication is what counts as marketing spend. A narrow CAC only includes paid media. A fully loaded CAC also includes salaries for the marketing team, tooling, and content production. Interviewers often ask this to see whether a candidate will state their assumption out loud rather than quietly pick one definition and hope nobody asks. A strong answer names which version is being used and why, before presenting the number.

Q6. How would you calculate return rate, and why might you look at it by category rather than as one company wide number?

Return rate is typically returned units, or returned order value, divided by total units or value sold in the same period. A single company wide return rate can hide a lot. Apparel usually returns at a much higher rate than, say, electronics accessories, simply because of fit. If a company wide return rate looks fine but one category is quietly climbing, that is the actionable finding, and it only shows up once you break the metric out by category.

Q7. Why does a business track new customer revenue and repeat customer revenue separately instead of just looking at total revenue?

Total revenue can grow while the business is actually getting less healthy underneath. If nearly all growth is coming from new customer acquisition and repeat revenue is flat or shrinking, that points to a retention problem that a rising top line will hide for a while. Splitting revenue this way is one of the simplest ways to catch a business that is spending its way to growth rather than earning loyalty from the customers it already has.

Q8. How would you define an “active customer” for a non subscription ecommerce business, and why does the definition change the resulting metric so much?

Unlike a subscription business, there is no natural renewal date to anchor the definition. A common approach is to define active as having placed at least one order in a trailing window, often 90 or 180 days, chosen based on how frequently a typical customer in that category buys.

A grocery delivery service and a mattress company should not use the same window. The number of active customers can look dramatically different depending on whether that window is 30, 90 or 365 days, so a good answer states the assumption clearly rather than presenting one number as if it were the only possible one.

Section B: Customer analytics, retention, cohorts and lifetime value

Q9. Write a query to calculate the repeat purchase rate among customers who have completed at least one order.

WITH order_counts AS (
  SELECT customer_id, COUNT(*) AS orders
  FROM orders
  WHERE status = 'completed'
  GROUP BY customer_id
)
SELECT
  COUNT(*) AS total_customers,
  SUM(CASE WHEN orders > 1 THEN 1 ELSE 0 END) AS repeat_customers,
  ROUND(100.0 * SUM(CASE WHEN orders > 1 THEN 1 ELSE 0 END) / COUNT(*), 2) AS repeat_rate_pct
FROM order_counts;

The query itself is simple. The part interviewers actually want to hear is what you would do with a low number: check whether it is low everywhere or concentrated in one acquisition channel, one category, or customers who never received a second touchpoint like an email or reminder.

Q10. Build a monthly cohort retention query: for each signup cohort, how many customers placed an order in each month since signup?

WITH cohorts AS (
  SELECT customer_id, DATE_TRUNC('month', signup_date) AS cohort_month
  FROM customers
),
orders_by_month AS (
  SELECT DISTINCT
    o.customer_id,
    DATE_TRUNC('month', o.order_date) AS order_month
  FROM orders o
  WHERE o.status = 'completed'
)
SELECT
  c.cohort_month,
  (DATE_PART('year', ob.order_month) - DATE_PART('year', c.cohort_month)) * 12
    + (DATE_PART('month', ob.order_month) - DATE_PART('month', c.cohort_month)) AS months_since_signup,
  COUNT(DISTINCT ob.customer_id) AS active_customers
FROM cohorts c
JOIN orders_by_month ob ON ob.customer_id = c.customer_id
GROUP BY c.cohort_month, months_since_signup
ORDER BY c.cohort_month, months_since_signup;

This returns raw counts, not percentages. In practice you would divide active_customers by the total size of that cohort_month to get a retention percentage, which is what usually gets pivoted into the classic triangle shaped cohort table. Explaining that extra step out loud, even if you do not write the pivot in SQL, shows you understand what the output is actually for.

Q11. How would you calculate the recency, frequency and monetary values needed for RFM segmentation?

SELECT
  o.customer_id,
  CURRENT_DATE - MAX(o.order_date) AS recency_days,
  COUNT(DISTINCT o.order_id) AS frequency,
  SUM(oi.quantity * oi.unit_price) AS monetary
FROM orders o
JOIN order_items oi ON oi.order_id = o.order_id
WHERE o.status = 'completed'
GROUP BY o.customer_id;

Once you have these three numbers per customer, each one is typically split into five buckets using something like NTILE(5), and the three bucket scores are combined into a single RFM score. The value of RFM is not the score itself, it is that it turns three separate numbers into one label a marketing team can actually act on, like targeting customers who are high monetary value but slipping in recency.

Q12. How would you calculate historical lifetime value (LTV) for each customer, and how is that different from predictive LTV?

SELECT
  o.customer_id,
  SUM(oi.quantity * oi.unit_price) AS historical_ltv
FROM orders o
JOIN order_items oi ON oi.order_id = o.order_id
WHERE o.status = 'completed'
GROUP BY o.customer_id;

Historical LTV is just a sum of what a customer has already spent, which is easy to compute but understates the value of newer customers who have not had time to buy again. Predictive LTV tries to estimate future spend based on patterns from similar customers, which is more useful for decisions like how much to spend acquiring a new customer, but it depends on a model and its assumptions rather than pure historical fact.

Q13. How would you define and flag churned customers for a business with no subscription or renewal date?

SELECT
  customer_id,
  MAX(order_date) AS last_order_date,
  CASE
    WHEN CURRENT_DATE - MAX(order_date) > 180 THEN 'churned'
    ELSE 'active'
  END AS churn_status
FROM orders
WHERE status = 'completed'
GROUP BY customer_id;

The 180 day cutoff here is a placeholder, not a universal rule. A strong candidate picks the window based on the typical repurchase cycle for that category, ideally by looking at the actual distribution of days between orders rather than guessing a round number.

Q14. When calculating repeat purchase rate, why does the choice of denominator change the result so much?

If the denominator is every customer who has ever signed up, the rate looks artificially low, because it includes people who joined last week and simply have not had time to order a second time. A fairer denominator is customers who are past a reasonable window since their first order, so everyone included has actually had a chance to come back. Getting this wrong is a common way repeat purchase rate quietly understates how loyal a customer base really is.

Q15. Why does the LTV to CAC ratio matter, and what does a healthy ratio typically look like?

LTV to CAC compares what a customer is worth against what it cost to acquire them. A ratio close to 1 means the business is barely breaking even on acquisition before accounting for any other costs.

A commonly cited rule of thumb in the industry is that a ratio around 3 to 1 suggests a sustainable acquisition engine, though that number moves a lot depending on payback period expectations, gross margin, and how long it typically takes to earn back the acquisition cost. The ratio is most useful as a trend over time and across channels, not as a single number to hit once and move on from.

Q16. How would you approach identifying customers who are at risk of churning before they actually stop buying?

The simplest starting point is days since last order compared against that customer’s own typical time between orders, since a customer who usually buys every two weeks going quiet for six weeks is a much stronger signal than the same six weeks of silence from someone who buys twice a year.

SELECT
  customer_id,
  order_date,
  order_date - LAG(order_date) OVER (PARTITION BY customer_id ORDER BY order_date) AS days_since_prior_order
FROM orders
WHERE status = 'completed';

From there, a more complete approach layers in other signals such as declining order size, reduced email engagement, or an increase in support tickets, rather than relying on recency alone.

Section C: Marketing funnel and campaign analytics

Q17. What does a typical ecommerce marketing funnel look like, and why do most funnels lose the most people at the same stage?

A common shape is impression, click, session, add to cart, checkout started, and purchase. Most funnels lose the largest share of people between session and add to cart, simply because browsing has almost no commitment attached to it while adding something to a cart is the first real signal of intent. That is usually where the biggest opportunity sits too, since even a small improvement at the widest part of the funnel moves more absolute customers than the same percentage gain further down.

Q18. Write a query to calculate session to purchase conversion rate broken out by channel.

SELECT
  channel,
  COUNT(*) AS sessions,
  SUM(CASE WHEN converted_flag = 1 THEN 1 ELSE 0 END) AS converted_sessions,
  ROUND(100.0 * SUM(CASE WHEN converted_flag = 1 THEN 1 ELSE 0 END) / COUNT(*), 2) AS conversion_rate_pct
FROM sessions
GROUP BY channel
ORDER BY conversion_rate_pct DESC;

On its own this ranks channels by conversion rate, but a channel with very few sessions can post a misleadingly high or low rate just from small sample size. Pairing conversion rate with session volume in the same view keeps you from over trusting a channel with fifty visits and one lucky sale.

Q19. Write a query to calculate customer acquisition cost by channel using campaign spend and new customers acquired.

SELECT
  ca.channel,
  SUM(ca.spend) AS total_spend,
  COUNT(DISTINCT c.customer_id) AS new_customers,
  ROUND(SUM(ca.spend) / NULLIF(COUNT(DISTINCT c.customer_id), 0), 2) AS cac
FROM campaigns ca
LEFT JOIN customers c
  ON c.acquisition_channel = ca.channel
  AND c.signup_date BETWEEN ca.start_date AND ca.end_date
GROUP BY ca.channel
ORDER BY cac;

This assumes a customer’s acquisition_channel lines up with a campaign’s channel over the same time window, which is a simplification worth saying out loud in an interview. Real attribution is rarely this clean, which is exactly why the attribution questions later in this guide exist.

Q20. What is the difference between ROI and ROAS for a marketing campaign, and when would you report each one?

ROAS, return on ad spend, is revenue generated divided by ad spend, and it ignores everything except the media cost itself. ROI factors in the full cost of running the campaign, including product cost and any other overhead, so it is a truer measure of profitability. Media and growth teams often lead with ROAS because it is simple and fast to calculate, but finance teams usually care more about ROI because that is what actually shows up in profit. A strong answer states which one is being asked for and does not use the terms interchangeably.

Q21. Write a query to build an email funnel showing sent, opened and clicked counts per campaign.

SELECT
  campaign_id,
  COUNT(DISTINCT CASE WHEN event_type = 'sent' THEN customer_id END) AS sent,
  COUNT(DISTINCT CASE WHEN event_type = 'opened' THEN customer_id END) AS opened,
  COUNT(DISTINCT CASE WHEN event_type = 'clicked' THEN customer_id END) AS clicked
FROM email_events
GROUP BY campaign_id;

From this you can derive open rate and click through rate as simple ratios of these three numbers, and comparing them across campaigns usually surfaces subject line or send time patterns worth testing further.

Q22. Why is click through rate on its own a weak measure of whether a campaign actually succeeded?

Click through rate measures interest, not outcome. A campaign can post a strong click through rate and still generate very little revenue if the traffic it drives does not convert once it lands, or if the people clicking were already going to buy anyway. Click through rate is a useful early signal for creative and targeting, but the metric that actually answers whether a campaign was worth running is revenue or contribution margin generated relative to what it cost.

Q23. Marketing runs a 20 percent off promotion and revenue jumps that week. How would you check whether the promotion actually grew revenue rather than just pulling forward purchases that would have happened anyway?

The first thing to look at is the weeks immediately following the promotion. If revenue dips noticeably below normal right after, that is a sign of pull forward, where customers who were going to buy soon simply bought earlier to catch the discount rather than the promotion creating new demand. Comparing total revenue across the promotion week plus the following two or three weeks against a normal period of the same length is a more honest read than looking at the promotion week alone.

Q24. Write a query to show week over week new customer signups by acquisition channel.

SELECT
  DATE_TRUNC('week', signup_date) AS signup_week,
  acquisition_channel,
  COUNT(*) AS new_customers
FROM customers
GROUP BY signup_week, acquisition_channel
ORDER BY signup_week, acquisition_channel;

This is a simple query, but it is worth practicing because trend by channel questions come up constantly in interviews as a warm up before moving into something harder like attribution or cohort analysis.

Section D: Attribution and channel performance

Q25. Explain last click attribution and its main weakness.

Last click attribution gives all the credit for a conversion to the final channel a customer touched before purchasing. It is easy to implement and easy to explain, which is why so many tools default to it. The weakness is that it ignores everything that happened earlier in the journey.

A customer might discover a brand through a social ad, think it over for a week, then search the brand name directly and click a paid search ad to finish the purchase. Last click hands all the credit to paid search, even though the social ad arguably did the harder work of creating the demand in the first place.

Q26. What is multi touch attribution, and when would a business prefer it over last click?

Multi touch attribution splits credit for a conversion across several touchpoints in the customer’s path, using a rule such as equal credit to every touch, or more credit to the first and last touch with less in between. It is preferred when a business has a longer consideration cycle with multiple channels genuinely involved, since last click would otherwise systematically undervalue awareness channels like social or display that tend to show up earlier in the journey rather than at the very end.

Q27. What is the difference between blended CAC and channel level CAC, and why track both?

Blended CAC divides total marketing spend across all channels by total new customers, regardless of which channel gets credit for each one. Channel level CAC calculates that same ratio separately for each channel. Blended CAC is useful as a single health check number for the business overall, but it can hide a channel that has quietly become far more expensive than the others. Channel level CAC is what actually informs a budget reallocation decision.

Q28. Write a query to identify each customer’s first touch channel based on their earliest session.

SELECT customer_id, channel AS first_touch_channel, session_date AS first_session_date
FROM (
  SELECT
    customer_id,
    channel,
    session_date,
    ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY session_date) AS rn
  FROM sessions
) ranked
WHERE rn = 1;

This is a first touch attribution model. Swapping ORDER BY session_date for ORDER BY session_date DESC in the same query gives you last touch instead, which is a useful thing to point out in an interview because it shows you understand the two models share almost identical logic.

Q29. What is incrementality testing, and why is it different from a standard attribution model?

An attribution model assigns credit for conversions that already happened, based on which touchpoints were present. Incrementality testing asks a different question: would this conversion have happened anyway without the marketing touch. A common approach is a geo holdout test, where a channel is paused entirely in some regions while running as normal in others, and the difference in outcomes between the two groups estimates the true incremental effect. Attribution can tell you a channel was present at the moment of conversion, but only incrementality testing can tell you whether that channel actually caused it.

Q30. How should attribution thinking change when a customer interacts with a brand across multiple devices?

Cookie based tracking generally cannot follow a person from their phone to their laptop, so a customer who first sees an ad on mobile and later completes the purchase on desktop can look like two entirely different, unrelated visitors. Where possible, resolving identity through a logged in account rather than a device cookie closes a lot of this gap, but it only works once the customer has actually signed in on both devices, so some blind spot is usually unavoidable and worth naming rather than ignoring.

Q31. Paid social and email both touched a customer before a purchase, and you have a limited budget conversation ahead. How would you decide how much credit each channel deserves?

Rather than trying to land on one perfect number, a practical approach is to look at what changes under a couple of different attribution rules, first touch, last touch, and linear, and see whether the story stays consistent or flips depending on the model chosen. If a channel only looks good under one specific model, that is worth flagging as a fragile result rather than a confident recommendation. Where the budget decision is high stakes enough, this is also where an incrementality test earns its cost, since it settles the question with an actual experiment rather than a modeling choice.

Section E: Product and catalog analytics

Q32. Write a query to find the top 10 bestselling products by revenue for a given month.

SELECT
  p.product_id,
  p.product_name,
  SUM(oi.quantity * oi.unit_price) AS revenue
FROM order_items oi
JOIN orders o ON o.order_id = oi.order_id
JOIN products p ON p.product_id = oi.product_id
WHERE o.status = 'completed'
  AND DATE_TRUNC('month', o.order_date) = '2026-08-01'
GROUP BY p.product_id, p.product_name
ORDER BY revenue DESC
LIMIT 10;

Q33. Write a query to find products with high unit sales but a low average selling price.

SELECT
  p.product_id,
  p.product_name,
  SUM(oi.quantity) AS units_sold,
  SUM(oi.quantity * oi.unit_price) AS revenue,
  ROUND(SUM(oi.quantity * oi.unit_price) / NULLIF(SUM(oi.quantity), 0), 2) AS avg_selling_price
FROM order_items oi
JOIN orders o ON o.order_id = oi.order_id
JOIN products p ON p.product_id = oi.product_id
WHERE o.status = 'completed'
GROUP BY p.product_id, p.product_name
ORDER BY units_sold DESC, avg_selling_price ASC
LIMIT 10;

A product that sells a huge volume at a low price point can quietly cost more to pack and ship than it earns in margin. This query is a starting point for the conversation a merchandising team would actually have next: should this product’s price move, or should it be positioned as a bundle add on instead of a standalone item.

Q34. What is sell through rate, and why does it matter for merchandising decisions even though it depends on inventory data this schema does not model?

Sell through rate is units sold divided by units received or held in stock over the same period, usually expressed as a percentage. A low sell through rate on a product that still has strong margin can mean the product is fine but overstocked, which is a very different problem from a product that simply is not selling. Calculating it for real would need an inventory or receiving table alongside order_items, which is worth naming directly if it comes up in an interview rather than pretending the schema in front of you covers everything.

Q35. Write a query to calculate gross margin percentage by product category.

SELECT
  p.category,
  SUM(oi.quantity * oi.unit_price) AS revenue,
  SUM(oi.quantity * p.cost_price) AS cost,
  ROUND(100.0 * (SUM(oi.quantity * oi.unit_price) - SUM(oi.quantity * p.cost_price))
    / NULLIF(SUM(oi.quantity * oi.unit_price), 0), 2) AS gross_margin_pct
FROM order_items oi
JOIN orders o ON o.order_id = oi.order_id
JOIN products p ON p.product_id = oi.product_id
WHERE o.status = 'completed'
GROUP BY p.category
ORDER BY gross_margin_pct DESC;

This is often the query that reframes a conversation. A category can be the biggest source of revenue in the business and still be one of the weakest sources of actual profit once margin is put next to it.

Q36. How would you find products that are frequently bought together in the same order?

SELECT
  a.product_id AS product_a,
  b.product_id AS product_b,
  COUNT(DISTINCT a.order_id) AS times_bought_together
FROM order_items a
JOIN order_items b
  ON a.order_id = b.order_id
  AND a.product_id < b.product_id
GROUP BY a.product_id, b.product_id
ORDER BY times_bought_together DESC
LIMIT 20;

The self join with a.product_id < b.product_id is doing two jobs at once: it stops each pair from being counted twice in both directions, and it stops a product from being paired with itself. This is a simple version of market basket analysis, and it is usually the first query behind a “customers also bought” recommendation.

Q37. How would you check whether a new product is genuinely growing the category or just cannibalizing sales from an existing similar product?

The first thing to compare is total category revenue before and after the new product launched, not just the new product’s own sales in isolation. If category revenue grew by roughly the amount the new product is now contributing, that looks like real incremental growth. If category revenue stayed flat while the new product ramped up and an existing similar product declined by about the same amount, that is a strong sign of cannibalization rather than genuine new demand.

Q38. A product’s sales have been declining month over month. What data would you pull first to figure out why?

A useful starting checklist, roughly in order: whether sessions landing on that product’s page have dropped, whether conversion rate on the page itself has dropped even with steady traffic, whether the product has been out of stock or low stock during the decline, whether price changed relative to competitors, and whether the decline lines up with a normal seasonal pattern from prior years. Working through that list in order usually narrows a vague “sales are down” question down to one specific, checkable cause within the first few queries.

Section F: SQL fundamentals for ecommerce data

Q39. Write a query to return all orders placed by a specific customer, most recent first.

SELECT *
FROM orders
WHERE customer_id = 1042
ORDER BY order_date DESC;

Q40. Explain the difference between an INNER JOIN and a LEFT JOIN, with an example of when the choice actually changes your answer.

An INNER JOIN only returns rows that have a match on both sides. Joining customers to orders this way drops every customer who has never placed an order, which is a problem the moment your question depends on knowing about those customers.

SELECT c.customer_id, o.order_id
FROM customers c
JOIN orders o ON o.customer_id = c.customer_id;

A LEFT JOIN keeps every row from the left table regardless of whether a match exists, filling in NULLs where there is none.

SELECT c.customer_id, o.order_id
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.customer_id;

If the question is “how many customers have never ordered,” an INNER JOIN would silently make that answer impossible to get, because those customers are removed before you even get a chance to count them.

Q41. Write a query to find every customer who has never placed an order.

SELECT c.customer_id
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.customer_id
WHERE o.order_id IS NULL;

Q42. Write a query to count the number of completed orders per month.

SELECT
  DATE_TRUNC('month', order_date) AS order_month,
  COUNT(*) AS order_count
FROM orders
WHERE status = 'completed'
GROUP BY order_month
ORDER BY order_month;

Q43. What is the difference between WHERE and HAVING, and why can’t you always swap one for the other?

WHERE filters individual rows before any grouping happens. HAVING filters groups after aggregation has already occurred, which means it can reference an aggregate function like COUNT() or SUM() in a way WHERE cannot.

SELECT customer_id, COUNT(*) AS order_count
FROM orders
WHERE status = 'completed'
GROUP BY customer_id
HAVING COUNT(*) > 5;

Here WHERE removes non completed orders before grouping even starts, and HAVING then keeps only the customers whose completed order count exceeds five. Trying to put COUNT(*) > 5 in the WHERE clause instead would fail, because at that point in execution the grouping has not happened yet.

Q44. Write a query to find order_id and product_id combinations that appear more than once in order_items, which would suggest a data quality bug.

SELECT order_id, product_id, COUNT(*) AS row_count
FROM order_items
GROUP BY order_id, product_id
HAVING COUNT(*) > 1;

In a well designed schema this should return nothing, since a single product should generally appear once per order with its quantity reflected in the quantity column. Finding rows here usually points to a bug somewhere upstream in how orders are being written into the table.

Q45. How do NULL values behave inside COUNT and AVG, and why can that behavior quietly distort a result?

SELECT
  COUNT(*) AS total_orders,
  COUNT(discount_amount) AS orders_with_discount_value,
  AVG(discount_amount) AS avg_discount
FROM orders;

COUNT(*) counts every row no matter what. COUNT(discount_amount) only counts rows where that column is not NULL. AVG(discount_amount) only averages the non NULL values too, it does not treat a NULL as zero. This matters a lot if NULL in this column actually means “no discount applied” rather than “unknown,” because then the average discount calculated here would be overstated, since orders with genuinely zero discount were excluded from the average instead of pulling it down.

Q46. Write a query to calculate what percentage of total orders came from each country.

SELECT
  c.country,
  COUNT(*) AS orders,
  ROUND(100.0 * COUNT(*) / (SELECT COUNT(*) FROM orders), 2) AS pct_of_total
FROM orders o
JOIN customers c ON c.customer_id = o.customer_id
GROUP BY c.country
ORDER BY pct_of_total DESC;

The scalar subquery in the denominator recalculates the grand total once for the whole query. The next section shows a window function way to do this same calculation without a subquery, which is worth knowing both ways since interviewers sometimes ask for the alternative on the spot.

Section G: Intermediate SQL, window functions and CTEs

Q47. Write a query to find each customer’s single largest completed order, and explain the difference between RANK, DENSE_RANK and ROW_NUMBER.

WITH order_totals AS (
  SELECT
    o.customer_id,
    o.order_id,
    SUM(oi.quantity * oi.unit_price) AS order_total
  FROM orders o
  JOIN order_items oi ON oi.order_id = o.order_id
  WHERE o.status = 'completed'
  GROUP BY o.customer_id, o.order_id
),
ranked AS (
  SELECT
    customer_id,
    order_id,
    order_total,
    RANK() OVER (PARTITION BY customer_id ORDER BY order_total DESC) AS order_rank
  FROM order_totals
)
SELECT customer_id, order_id, order_total
FROM ranked
WHERE order_rank = 1;

RANK leaves a gap in the numbering after a tie, so two orders tied for first both get rank 1 and the next order gets rank 3. DENSE_RANK also gives both tied orders rank 1, but the next order gets rank 2, with no gap. ROW_NUMBER never allows ties at all, it assigns a unique sequential number even when two values are identical, arbitrarily deciding which one counts as first. Using RANK here is deliberate: if a customer genuinely has two orders tied for their largest, this query correctly returns both instead of silently picking one.

Q48. Write a query to calculate a running total of daily revenue using a CTE and a window function.

WITH daily_revenue AS (
  SELECT
    o.order_date,
    SUM(oi.quantity * oi.unit_price) AS revenue
  FROM orders o
  JOIN order_items oi ON oi.order_id = o.order_id
  WHERE o.status = 'completed'
  GROUP BY o.order_date
)
SELECT
  order_date,
  revenue,
  SUM(revenue) OVER (ORDER BY order_date) AS running_total_revenue
FROM daily_revenue
ORDER BY order_date;

The CTE isolates the aggregation step, so the window function afterward only has to worry about ordering and summing, rather than aggregating and running a window calculation in the same breath.

Q49. Write a query showing what percentage of a customer’s total spend came from their single largest order.

WITH order_totals AS (
  SELECT
    o.customer_id,
    o.order_id,
    SUM(oi.quantity * oi.unit_price) AS order_total
  FROM orders o
  JOIN order_items oi ON oi.order_id = o.order_id
  WHERE o.status = 'completed'
  GROUP BY o.customer_id, o.order_id
)
SELECT
  customer_id,
  order_id,
  order_total,
  ROUND(100.0 * order_total / SUM(order_total) OVER (PARTITION BY customer_id), 2) AS pct_of_customer_total
FROM order_totals
ORDER BY customer_id, pct_of_customer_total DESC;

A customer where one single order accounts for 80 percent of everything they have ever spent looks very different from one whose spend is evenly spread across ten orders, even if their total lifetime value is identical. This query is one of the simplest ways to surface that difference.

Q50. Write a query to find each customer’s second most recent completed order.

WITH ranked_orders AS (
  SELECT
    customer_id,
    order_id,
    order_date,
    ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY order_date DESC) AS recency_rank
  FROM orders
  WHERE status = 'completed'
)
SELECT customer_id, order_id, order_date
FROM ranked_orders
WHERE recency_rank = 2;

Q51. What is a CTE, and why might you reach for one instead of a subquery on a multi step problem?

A CTE, written with WITH, names an intermediate result so it can be referenced later in the query as if it were its own table. The logic is not really different from a subquery, but a chain of CTEs reads top to bottom the same way you would explain the steps out loud, while deeply nested subqueries tend to read inside out, which gets hard to follow once there are more than two or three steps.

WITH step_one AS (
  SELECT customer_id, COUNT(*) AS order_count
  FROM orders
  WHERE status = 'completed'
  GROUP BY customer_id
),
step_two AS (
  SELECT customer_id
  FROM step_one
  WHERE order_count > 5
)
SELECT * FROM step_two;

Q52. Write a query to calculate month over month revenue growth rate.

WITH monthly_revenue AS (
  SELECT
    DATE_TRUNC('month', o.order_date) AS order_month,
    SUM(oi.quantity * oi.unit_price) AS revenue
  FROM orders o
  JOIN order_items oi ON oi.order_id = o.order_id
  WHERE o.status = 'completed'
  GROUP BY order_month
)
SELECT
  order_month,
  revenue,
  LAG(revenue) OVER (ORDER BY order_month) AS prior_month_revenue,
  ROUND(100.0 * (revenue - LAG(revenue) OVER (ORDER BY order_month))
    / NULLIF(LAG(revenue) OVER (ORDER BY order_month), 0), 1) AS mom_growth_pct
FROM monthly_revenue
ORDER BY order_month;

LAG pulls a value from a prior row without needing a self join, which is what makes period over period comparisons like this one so much cleaner with window functions than with the join based approach many candidates default to first.

Q53. Write a query to calculate a 7 day rolling average of daily revenue.

WITH daily_revenue AS (
  SELECT
    o.order_date,
    SUM(oi.quantity * oi.unit_price) AS revenue
  FROM orders o
  JOIN order_items oi ON oi.order_id = o.order_id
  WHERE o.status = 'completed'
  GROUP BY o.order_date
)
SELECT
  order_date,
  revenue,
  ROUND(AVG(revenue) OVER (
    ORDER BY order_date
    ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
  ), 2) AS rolling_7_day_avg
FROM daily_revenue
ORDER BY order_date;

A rolling average like this is what most revenue dashboards actually chart, since raw daily revenue is usually too noisy day to day to show a real trend on its own.

Q54. Write a query to calculate how many days it takes each customer to place their second order, using a CTE and window function.

WITH ranked_orders AS (
  SELECT
    customer_id,
    order_date,
    ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY order_date) AS order_seq
  FROM orders
  WHERE status = 'completed'
),
first_two AS (
  SELECT
    customer_id,
    MAX(CASE WHEN order_seq = 1 THEN order_date END) AS first_order_date,
    MAX(CASE WHEN order_seq = 2 THEN order_date END) AS second_order_date
  FROM ranked_orders
  WHERE order_seq IN (1, 2)
  GROUP BY customer_id
)
SELECT
  customer_id,
  second_order_date - first_order_date AS days_to_second_order
FROM first_two
WHERE second_order_date IS NOT NULL;

Averaging days_to_second_order across all customers here gives you a single, very concrete number that is often more useful in a retention conversation than an abstract retention percentage, because it answers the practical question of how long a new customer usually has before they are at real risk of never coming back.

Section H: Advanced SQL and business case scenarios

Q55. Leadership wants to know whether a recent price increase hurt overall revenue. How would you structure the analysis?

WITH before_period AS (
  SELECT SUM(oi.quantity * oi.unit_price) AS revenue, COUNT(DISTINCT o.order_id) AS orders
  FROM orders o
  JOIN order_items oi ON oi.order_id = o.order_id
  WHERE o.status = 'completed'
    AND o.order_date BETWEEN '2026-06-01' AND '2026-06-30'
),
after_period AS (
  SELECT SUM(oi.quantity * oi.unit_price) AS revenue, COUNT(DISTINCT o.order_id) AS orders
  FROM orders o
  JOIN order_items oi ON oi.order_id = o.order_id
  WHERE o.status = 'completed'
    AND o.order_date BETWEEN '2026-07-01' AND '2026-07-31'
)
SELECT * FROM before_period
UNION ALL
SELECT * FROM after_period;

The query is the easy part. The analysis that matters is decomposing whether any revenue change came from fewer orders, a lower average order value, or both, since a price increase can raise AOV while quietly scaring off enough order volume to leave total revenue flat or worse. It is also worth comparing against the same month a year earlier rather than only the prior month, so a normal seasonal dip does not get mistakenly blamed on the price change.

Q56. Write a query to build a cohort based revenue table showing total revenue per signup cohort by month since signup.

WITH cohorts AS (
  SELECT customer_id, DATE_TRUNC('month', signup_date) AS cohort_month
  FROM customers
),
order_revenue AS (
  SELECT
    o.customer_id,
    DATE_TRUNC('month', o.order_date) AS order_month,
    SUM(oi.quantity * oi.unit_price) AS revenue
  FROM orders o
  JOIN order_items oi ON oi.order_id = o.order_id
  WHERE o.status = 'completed'
  GROUP BY o.customer_id, order_month
)
SELECT
  c.cohort_month,
  (DATE_PART('year', r.order_month) - DATE_PART('year', c.cohort_month)) * 12
    + (DATE_PART('month', r.order_month) - DATE_PART('month', c.cohort_month)) AS months_since_signup,
  SUM(r.revenue) AS cohort_revenue,
  COUNT(DISTINCT c.customer_id) AS cohort_size
FROM cohorts c
JOIN order_revenue r ON r.customer_id = c.customer_id
GROUP BY c.cohort_month, months_since_signup
ORDER BY c.cohort_month, months_since_signup;

Dividing cohort_revenue by cohort_size at each months_since_signup gives revenue per customer over time, and running a cumulative sum across that turns it into an LTV curve for each cohort, which is what lets you compare whether newer cohorts are actually more valuable than older ones at the same point in their lifecycle.

Q57. Marketing reports a campaign was a big success based on a strong click through rate, but revenue barely moved. What would you check?

SELECT
  ee.campaign_id,
  COUNT(DISTINCT CASE WHEN ee.event_type = 'clicked' THEN ee.customer_id END) AS clicks,
  SUM(oi.quantity * oi.unit_price) AS attributed_revenue
FROM email_events ee
LEFT JOIN orders o
  ON o.customer_id = ee.customer_id
  AND o.order_date >= ee.event_date
  AND o.order_date < ee.event_date + INTERVAL '7 days'
LEFT JOIN order_items oi ON oi.order_id = o.order_id
WHERE ee.event_type = 'clicked'
GROUP BY ee.campaign_id;

This attributes revenue to a click only if an order followed within seven days, which is one reasonable window among several you could choose, and stating that assumption out loud matters. Beyond the query, the checklist worth walking through out loud is: was the traffic actually reaching a relevant landing page, did the audience match people likely to buy, and is the click through rate itself inflated by a subject line that overpromised what the email actually offered.

Q58. Write a query to find win back candidates, customers who went quiet for a long stretch and then placed a new order.

WITH order_gaps AS (
  SELECT
    customer_id,
    order_date,
    order_date - LAG(order_date) OVER (PARTITION BY customer_id ORDER BY order_date) AS gap_days
  FROM orders
  WHERE status = 'completed'
)
SELECT customer_id, order_date AS win_back_order_date, gap_days
FROM order_gaps
WHERE gap_days > 180;

Q59. Two product categories both grew revenue 20 percent last quarter, and you have been asked to recommend which one gets more marketing budget. What additional data would actually change your recommendation?

Revenue growth alone treats both categories as equally attractive, which is rarely true. Margin matters, since 20 percent growth in a low margin category is worth far less in profit than the same growth in a high margin one. The trend inside the quarter matters too, a category accelerating month over month is a different bet than one that grew 20 percent entirely in one early month and has been flat since.

Repeat purchase rate distinguishes durable growth from a one time promotional spike. And rising CAC in a category’s channels can mean that next quarter’s 20 percent will cost meaningfully more to repeat. A confident recommendation usually needs at least margin and trend shape before revenue growth alone means much.

Q60. Write a query to calculate the median order value, and explain why median can be a more honest number than average here.

SELECT
  PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY order_total) AS median_order_value
FROM (
  SELECT o.order_id, SUM(oi.quantity * oi.unit_price) AS order_total
  FROM orders o
  JOIN order_items oi ON oi.order_id = o.order_id
  WHERE o.status = 'completed'
  GROUP BY o.order_id
) order_totals;

Order values in ecommerce are almost always right skewed, a handful of very large orders, maybe from a wholesale buyer or a rare bulk purchase, can pull the average well above what a typical customer actually spends. Median is not affected by those outliers the same way, so it tends to give a more realistic picture of what a normal order actually looks like. PERCENTILE_CONT is documented as one of the ordered set aggregate functions in the official PostgreSQL documentation, which is worth bookmarking since the exact syntax shifts slightly across BigQuery, Snowflake and other databases.

Q61. A spike in refunds is reported over the last two weeks. What queries would you run to find out if it is isolated to a product, channel or region?

SELECT
  p.category,
  c.country,
  COUNT(*) AS refunded_orders
FROM orders o
JOIN order_items oi ON oi.order_id = o.order_id
JOIN products p ON p.product_id = oi.product_id
JOIN customers c ON c.customer_id = o.customer_id
WHERE o.status = 'refunded'
  AND o.order_date >= CURRENT_DATE - INTERVAL '14 days'
GROUP BY p.category, c.country
ORDER BY refunded_orders DESC;

The key comparison is not just this table on its own, it is this same breakdown run against the prior 14 day period as a baseline. If refunds are up across every category and region roughly proportionally, that points to something broad like a shipping carrier issue. If they are concentrated in one product or one region, that narrows the search dramatically, often down to a specific defective batch or a local delivery problem.

Q62. Write a query to flag VIP customers as the top 10 percent by total spend who have also placed more than three orders in the last 90 days.

WITH recent_activity AS (
  SELECT
    customer_id,
    COUNT(*) AS orders_last_90_days
  FROM orders
  WHERE status = 'completed'
    AND order_date >= CURRENT_DATE - INTERVAL '90 days'
  GROUP BY customer_id
),
customer_spend AS (
  SELECT
    o.customer_id,
    SUM(oi.quantity * oi.unit_price) AS total_spend,
    NTILE(10) OVER (ORDER BY SUM(oi.quantity * oi.unit_price) DESC) AS spend_decile
  FROM orders o
  JOIN order_items oi ON oi.order_id = o.order_id
  WHERE o.status = 'completed'
  GROUP BY o.customer_id
)
SELECT
  cs.customer_id,
  cs.total_spend,
  ra.orders_last_90_days
FROM customer_spend cs
JOIN recent_activity ra ON ra.customer_id = cs.customer_id
WHERE cs.spend_decile = 1
  AND ra.orders_last_90_days > 3;

Combining a lifetime measure like total spend with a recent activity window like the last 90 days is what keeps this list from filling up with customers who spent a lot two years ago and have since gone completely quiet.

Section I: Statistics and experimentation in marketing

Q63. In plain language, what does statistical significance actually mean for a landing page A/B test?

It answers a fairly narrow question: if the two versions of the page truly performed identically, how likely would it be to see a difference at least this large just from random variation in who happened to land in each group. A commonly used cutoff is a 5 percent chance, meaning a result this extreme would only be expected about 1 time in 20 if nothing had actually changed. It is a statement about chance, not a statement that the winning version is definitely better in some absolute sense.

Q64. What is a p value, and what is a common way people misinterpret it?

A p value is the probability of seeing a result at least as extreme as the one observed, assuming there is truly no difference between the versions being tested. The most common misinterpretation is treating it as the probability that the null hypothesis is true, or the probability that the result happened by chance. Neither of those is what it measures. It only tells you how surprising the observed result would be if nothing had actually changed, nothing more.

This exact misinterpretation is common enough that the American Statistical Association published a formal statement addressing it directly, worth reading if you want the precise, non simplified version rather than a paraphrase of it.

Q65. How would you determine the sample size needed before running a test on an email subject line?

You need four inputs going in: the current baseline open rate, the smallest lift you would actually care about detecting, a significance level, usually 5 percent, and statistical power, usually 80 percent, which is the chance of detecting the effect if it is really there. Those feed into a standard sample size formula or calculator.

The practical constraint worth naming in an interview is that smaller effects require dramatically more traffic to detect reliably, so if the email list is small, it may only be realistic to test changes expected to move open rate by a meaningful amount, not a fraction of a percent.

Q66. Explain Type I and Type II error, with an ecommerce example of each.

A Type I error is a false positive, concluding there is a real effect when there is not. An example is declaring a checkout redesign a win and rolling it out company wide based on a result that was actually just random noise.

A Type II error is a false negative, failing to detect an effect that genuinely exists. An example is a product page redesign that truly does improve conversion getting discarded because the test ended before enough traffic accumulated to reach significance. The two errors trade off against each other, which is part of why sample size and test duration decisions matter as much as the result itself.

Q67. A test shows a 5 percent lift in conversion rate, but it is not statistically significant, and a stakeholder wants to roll it out anyway. What do you tell them?

The honest answer is that a lift this size, without significance, could easily be noise rather than a real effect, and rolling it out on that basis means making a permanent decision on an uncertain signal. That does not automatically mean the answer is no.

If the change is low cost and low risk to implement, and the direction is at least positive rather than negative, some teams reasonably choose to ship it anyway while continuing to monitor performance afterward. What matters is being clear with the stakeholder that this is a judgment call being made under uncertainty, not a proven win, so expectations are set honestly either way.

Q68. What is the novelty effect in A/B testing, and how might it distort results for something like a redesigned checkout flow?

The novelty effect is when people react to a change simply because it is different and unfamiliar, not because it is actually better, and that reaction fades once the new experience stops feeling new. A redesigned checkout might see a short term bump in engagement purely from curiosity, which can make an early readout look more positive than the change deserves once behavior settles back down. Running the test long enough to see whether the effect holds up, or specifically checking whether performance decays over the course of the test, is how you catch this before making a permanent call.

Q69. How would you decide between a one tailed and a two tailed test for something like a checkout button color change?

A one tailed test only checks whether the new version is better in one specific direction, which gives it more power to detect an effect in that direction but makes it blind to a real effect running the opposite way. A two tailed test checks for a difference in either direction.

The safer default for most business tests is two tailed, because it is rare to be so certain a change could never make things worse that you are willing to not even look for that possibility. A one tailed test is only really defensible when there is a strong, previously stated reason you would never act on a result in the other direction even if you saw one.

Section J: Behavioral and case study questions for analytics interviews

Q70. Tell me about a time you used data to influence a decision, even if the outcome was not what you expected.

A structure that holds up well here is situation, action, result, in that order: what the question was, what you actually did with the data, and what happened, including if the recommendation was not fully adopted or the result was mixed.

You do not need a story from a full time analytics job to answer this well. A personal project, a freelance engagement, a dashboard you built for a small business, or a rigorous class project all count, as long as you can speak honestly about what the data showed and what you did because of it. What interviewers are actually screening for is whether you can connect an analysis to an actual decision, not the size or prestige of the company where it happened.

Q71. How would you approach a dataset you have never seen before, in the first 30 minutes?

A reasonable sequence is to first understand what each table represents and how they relate to each other, then check row counts and obvious data quality issues like missing values, duplicates, or dates that fall outside a sensible range, then look at a handful of summary statistics to get a feel for the scale of the numbers involved. Only after that would you start trying to answer the actual business question, because any analysis built on a dataset you have not sanity checked first risks confidently answering the wrong question well.

Q72. A stakeholder asks for a report they clearly have not thought all the way through. How do you handle that conversation?

The useful move is usually to ask what decision the report is meant to support, before building anything. Most half formed requests get much clearer once you ask what they will do differently depending on what the numbers show. If the answer reveals the stakeholder has not actually thought about the decision yet, that is worth surfacing gently rather than quietly building a report nobody will act on.

Q73. How do you decide what to do when the data contradicts a stakeholder’s strong intuition?

The first step is making sure the data is actually right, since a surprising result is just as often a data or logic error as it is a genuine insight. Once you are confident in the number, presenting it alongside the reasoning behind it, rather than just the conclusion, tends to land better than stating the answer alone. People are far more willing to update a strong intuition when they can see how you got there, and it also gives them a real chance to point out context you might be missing.

Q74. How would you explain a complex analysis to someone with no data background?

Leading with the conclusion and the decision it supports, before any methodology, is usually the right order. Most non technical stakeholders do not need to understand a window function or a regression to trust a result, they need to understand what it means for something they already care about, and they need a believable, plain language reason to trust that the number is right. Methodology belongs in the appendix of the conversation, available if asked, not the opening.

Q75. Describe a mistake you made in an analysis and what you changed afterward.

A good answer here is specific about the actual error, whether it was a wrong join that silently duplicated rows, a metric definition that did not match what the stakeholder assumed it meant, or a conclusion drawn from too small a sample.

What matters more than the mistake itself is the concrete habit that changed afterward, like always checking row counts before and after a join, or writing the metric definition down and confirming it with the stakeholder before presenting a number. Interviewers ask this to see whether you actually learn from errors or just apologize for them, and a vague or overly polished answer here is usually more of a red flag than an honest, specific one.

What to practice next

Seventy five questions is enough to prepare on, not enough to absorb in one sitting. A better approach than reading straight through is to pick one section a day, write your own query or your own answer first without looking, and only compare against the model afterward.

The gap between your first attempt and the model answer is usually more useful than the model answer itself, because it shows you exactly where your instinct and the stronger approach diverge. Working through this full set of ecommerce and marketing analytics interview questions section by section, rather than skimming straight to the answers, is what actually builds the skill.

If you want the fuller version of any single topic here, worked through against a real business scenario end to end rather than as an isolated interview question, that is exactly what the Ecommerce and Marketing Analytics series on BloomInData is for. Each piece in that series takes one business problem, builds the SQL from scratch, turns it into a dashboard, and closes with its own set of interview questions, which makes it a natural next stop for anything above that you want to go deeper on.

You can pull the exact seven table practice schema described above, ready to load and query, from the BloomInData dataset library.

About the Author,

Priyanka Lakra
I am a data learner, explorer, and creator at BloomInData, focused on turning real-world business problems into practical, portfolio-ready analytics projects. Through BloomInData, I explore SQL, Excel, Python, Power BI, business analytics, and data-driven decision-making using realistic business scenarios and datasets. My approach is simple: don't just build dashboards, learn how to investigate a business problem, find the right data, analyze it, and explain what the numbers actually mean.

This article is part of my Data Analytics Interview Preparation journey. I’m sharing the practical questions, business problems, SQL analysis, Python workflows, dashboards, and case studies that I’m working on while preparing for data analytics roles.

I’m documenting what I’m learning and practicing through BloomInData so that my preparation can also become a practical resource for others who are preparing for data analytics interviews and working toward becoming job-ready.

Explore more practical data analytics and interview preparation content on BloomInData.