Data Analyst Interview: SQL Assessment Questions and Answers

Mohammad Wahidul Islam
3 min readJul 24, 2023

--

Hey Guys, I want to share my experience of participating in a SQL assessment which is part of an interview for a data analyst position at one of the biggest E-commerce platforms.

Here are the Questions:

Schema diagram

Q1. How many riders signed up for each 1-week period? (i.e. week starts 2022–01–01)

Answer:

SELECT week(registration_date) as ‘Week of registration’,

count(rider_id) as ‘Number of riders’

FROM delivery_rider.riders

GROUP BY 1;

Result

Q2. What was the average time in minutes it took for each rider pickup the order?

Answer:

select rider_id, round(avg(timestampdiff(minute,order_time, pickup_time)),1) as AvgTime

from rider_orders

inner join customer_orders

on customer_orders.order_id = rider_orders.order_id

where distance != 0

group by rider_id

order by AvgTime;

result

Q3. Is there any relationship between the number of item and how long the order takes to deliver?

Answer:

WITH cte AS (

SELECT c.order_id, COUNT(c.order_id) AS itemCount, ROUND(TIMESTAMPDIFF(MINUTE, c.order_time, delivered

)) AS Avgtime

FROM customer_orders AS c

INNER JOIN rider_orders AS r ON c.order_id = r.order_id

WHERE distance != 0

GROUP BY c.order_id, c.order_time, delivered

)

SELECT itemCount, AVG(Avgtime) AS AverageTime

FROM cte

GROUP BY itemCount;

Result

Q4. What was the average distance traveled for each customer?

with cte as (

select c.customer_id, round(avg(r.distance),1) as AvgDistance

from customer_orders as c

inner join rider_orders as r

on c.order_id = r.order_id

where r.distance != 0

group by c.customer_id)

select * from cte;

Result:

Result

Q5. What was the difference between the longest and shortest delivery times for all orders? (consider ‘duration’ column for complete order minute )

Answers:

SELECT MIN(duration) minimum_duration,

MAX(duration) AS maximum_duration,

MAX(duration) — MIN(duration) AS maximum_difference

FROM rider_orders;

Result

Q6. What was the average speed for each rider for each delivery and do you notice any trend for these values?

Answers:

SELECT rider_id,order_id,

distance AS distance_km,

ROUND(duration / 60, 2) AS duration_hr,

ROUND(distance * 60 / duration, 2) AS average_speed

FROM rider_orders

WHERE delivered <> ‘’

ORDER BY rider_id,order_id;

Result

Q7. What is the successful delivery percentage for each rider?

Answer:

with cte as(

select rider_id, sum(case

when distance != 0 then 1

else 0

end) as percsucc, count(order_id) as TotalOrders

from rider_orders

group by rider_id)

select rider_id,round((percsucc/TotalOrders)*100) as Successfulpercentage

from cte

order by rider_id;

Result

Q8. Rank the category based on the cancellation rate.

SELECT c.item_id,

c.category_type,

c.category_name,

COUNT(ro.cancellation) AS cancelled_orders,

SUM(ro.cancellation) AS total_orders,

SUM(CASE WHEN ro.cancellation = ‘marchent Cancellation’ OR ro.cancellation = ‘Customer Cancellation’ THEN 1 ELSE 0 END) AS cancelled_orders,

(SUM(CASE WHEN ro.cancellation = ‘marchent Cancellation’ OR ro.cancellation = ‘Customer Cancellation’ THEN 1 ELSE 0 END) / COUNT(ro.cancellation))*100 AS cancellation_rate

FROM category c

JOIN customer_orders co ON c.item_id = co.item_id

JOIN rider_orders ro ON co.order_id = ro.order_id

GROUP BY c.item_id, c.category_type, c.category_name

ORDER BY cancellation_rate DESC;

Result

If this Article helps Please Follow me , Thank You

--

--

Mohammad Wahidul Islam
Mohammad Wahidul Islam

Written by Mohammad Wahidul Islam

0 Followers

Data Analyst | Helping Businesses Find Key Insights from their Data Through Data analysis and Visualization | LinkedIn : linkedin.com/in/wahidcs

No responses yet