SQL Challenges/Immediate Food Delivery II

Challenge DetailMediumReviewed 2026-04-20

Immediate Food Delivery II

Find the percentage of immediate orders in the first orders of all customers, rounded to 2 decimal places.

AggregationDate LogicWindow FunctionsSubqueries

This challenge now meets the current minimum standard for sitemap inclusion and independent indexing.

2026-01-05Medium

Immediate Food Delivery II

Find the percentage of immediate orders in the first orders of all customers, rounded to 2 decimal places.

Available Tables:

SQL Editor

What This Challenge Is Testing

This is a first-event cohort metric. Product and operations teams often care about first-purchase experience or first-session behavior, so the real challenge is isolating one canonical first row per customer before calculating the final percentage.

Topic Tags

AnalyticsDatesWindow Functions

Schema At A Glance

delivery

delivery_idcustomer_idorder_datecustomer_pref_delivery_date

Solution Approaches

Filter first orders, then average the immediate flag

WITH first_orders AS (
  SELECT
    customer_id,
    MIN(order_date) AS first_order_date
  FROM delivery
  GROUP BY customer_id
)
SELECT
  ROUND(
    AVG(CASE WHEN d.order_date = d.customer_pref_delivery_date THEN 100.0 ELSE 0.0 END),
    2
  ) AS immediate_percentage
FROM delivery d
JOIN first_orders f
  ON d.customer_id = f.customer_id
 AND d.order_date = f.first_order_date

This pattern makes the cohort definition explicit. First identify the first order per customer, then evaluate only those rows for whether the order was immediate.

Use ROW_NUMBER to tag the first order

WITH ranked_orders AS (
  SELECT
    customer_id,
    order_date,
    customer_pref_delivery_date,
    ROW_NUMBER() OVER (
      PARTITION BY customer_id
      ORDER BY order_date
    ) AS order_rank
  FROM delivery
)
SELECT
  ROUND(
    AVG(CASE WHEN order_date = customer_pref_delivery_date THEN 100.0 ELSE 0.0 END),
    2
  ) AS immediate_percentage
FROM ranked_orders
WHERE order_rank = 1

The window-function version is easier to extend when later questions ask for second orders, first-week behavior, or side-by-side metrics across multiple event ranks.

Common Pitfalls

The common bug is calculating the percentage across every order instead of only the first order for each customer. Another subtle mistake is counting rows rather than customers if ties or duplicate first-order timestamps are possible.

Frequently Asked Questions

Why is this not just a simple percentage over the delivery table?

Because the prompt is specifically about first orders. You must reduce the table to one qualifying row per customer before computing the immediate-delivery rate.

When is ROW_NUMBER better than MIN(order_date)?

ROW_NUMBER is more flexible when you want to keep the original row intact and extend the analysis to later order positions. MIN is shorter when you only need the first date.

Concept Guides

Use these guides when you want the fuller explanation behind the SQL pattern this challenge is testing.

Building Conversion Funnels in SQL

Master funnel analysis with SQL. Learn how to track user journeys from landing page to purchase and identify where you are losing customers.

Cohort Analysis with SQL

Learn how to perform cohort analysis using SQL to track user retention, identify trends, and measure product success over time.

Understanding SQL Window Functions: A Visual Guide

Master ROW_NUMBER, RANK, and running totals with interactive visualizations. See exactly how window functions process your data row by row.

Topic Hubs

If this problem exposed a deeper weakness, use a topic hub to move through a curated cluster instead of random article hopping.

Analytics

Analytics SQL and business metrics

A practical cluster for cohorts, funnels, customer value, and the kinds of reporting questions teams actually ask every week.

Advanced SQL

Advanced query patterns

Use this path when you are moving beyond beginner SELECT queries into CTEs, window functions, and conditional logic.

Practice Path

If you want the rotating practice flow, open this same question from the daily stream. If you want interview-style packaging, continue into the interview pool after this rep.

Back to Challenges Open In Daily Browse Interviews

Related Challenges

Monthly Transactions IMedium

Roll monthly transaction reporting into one query while splitting total volume from approved volume by country.

Game Play Analysis IVMedium

Compute next-day retention by checking whether each player returned on the day after their first login.

Trips and UsersHard

Compute daily cancellation rate after excluding any trip where either the client or driver is banned.

SQL Challenges/Immediate Food Delivery II

Challenge DetailMediumReviewed 2026-04-20

Immediate Food Delivery II

Find the percentage of immediate orders in the first orders of all customers, rounded to 2 decimal places.

AggregationDate LogicWindow FunctionsSubqueries

This challenge now meets the current minimum standard for sitemap inclusion and independent indexing.

2026-01-05Medium

Immediate Food Delivery II

Find the percentage of immediate orders in the first orders of all customers, rounded to 2 decimal places.

Available Tables:

SQL Editor

What This Challenge Is Testing

Topic Tags

AnalyticsDatesWindow Functions

Schema At A Glance

delivery

delivery_idcustomer_idorder_datecustomer_pref_delivery_date

Solution Approaches

Filter first orders, then average the immediate flag

WITH first_orders AS (
  SELECT
    customer_id,
    MIN(order_date) AS first_order_date
  FROM delivery
  GROUP BY customer_id
)
SELECT
  ROUND(
    AVG(CASE WHEN d.order_date = d.customer_pref_delivery_date THEN 100.0 ELSE 0.0 END),
    2
  ) AS immediate_percentage
FROM delivery d
JOIN first_orders f
  ON d.customer_id = f.customer_id
 AND d.order_date = f.first_order_date

This pattern makes the cohort definition explicit. First identify the first order per customer, then evaluate only those rows for whether the order was immediate.

Use ROW_NUMBER to tag the first order

WITH ranked_orders AS (
  SELECT
    customer_id,
    order_date,
    customer_pref_delivery_date,
    ROW_NUMBER() OVER (
      PARTITION BY customer_id
      ORDER BY order_date
    ) AS order_rank
  FROM delivery
)
SELECT
  ROUND(
    AVG(CASE WHEN order_date = customer_pref_delivery_date THEN 100.0 ELSE 0.0 END),
    2
  ) AS immediate_percentage
FROM ranked_orders
WHERE order_rank = 1

The window-function version is easier to extend when later questions ask for second orders, first-week behavior, or side-by-side metrics across multiple event ranks.

Common Pitfalls

Frequently Asked Questions

Why is this not just a simple percentage over the delivery table?

Because the prompt is specifically about first orders. You must reduce the table to one qualifying row per customer before computing the immediate-delivery rate.

When is ROW_NUMBER better than MIN(order_date)?

ROW_NUMBER is more flexible when you want to keep the original row intact and extend the analysis to later order positions. MIN is shorter when you only need the first date.

Concept Guides

Use these guides when you want the fuller explanation behind the SQL pattern this challenge is testing.

Building Conversion Funnels in SQL

Master funnel analysis with SQL. Learn how to track user journeys from landing page to purchase and identify where you are losing customers.

Cohort Analysis with SQL

Learn how to perform cohort analysis using SQL to track user retention, identify trends, and measure product success over time.

Understanding SQL Window Functions: A Visual Guide

Master ROW_NUMBER, RANK, and running totals with interactive visualizations. See exactly how window functions process your data row by row.

Topic Hubs

If this problem exposed a deeper weakness, use a topic hub to move through a curated cluster instead of random article hopping.

Analytics

Analytics SQL and business metrics

A practical cluster for cohorts, funnels, customer value, and the kinds of reporting questions teams actually ask every week.

Advanced SQL

Advanced query patterns

Use this path when you are moving beyond beginner SELECT queries into CTEs, window functions, and conditional logic.

Practice Path

If you want the rotating practice flow, open this same question from the daily stream. If you want interview-style packaging, continue into the interview pool after this rep.

Back to Challenges Open In Daily Browse Interviews

Related Challenges

Monthly Transactions IMedium

Roll monthly transaction reporting into one query while splitting total volume from approved volume by country.

Game Play Analysis IVMedium

Compute next-day retention by checking whether each player returned on the day after their first login.

Trips and UsersHard

Compute daily cancellation rate after excluding any trip where either the client or driver is banned.