Data Analyst Interview: SQL Assessment Questions and Answers
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:
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;
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;
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;
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:
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;
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;
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;
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;
If this Article helps Please Follow me , Thank You