Lendable interview

What is an inner join?

Keeps rows that have matching records in both tables.

What did you find challenging? How did you address?

Orders, shipments and refunds were recorded at different levels. One order could contain multiple shipments or refund records, so joining the datasets directly could multiply rows and potentially lead to double-counting.

I addressed this differently depending on the question. For refund rates in Q5, I used a LEFT JOIN to retain orders without refunds and COUNT DISTINCT to count each order once.

For Q7, I used EXISTS to check whether an order had any refund without multiplying the shipment records.

And in Q8, I used CTEs to first aggregate refunds by order, calculate net revenue at the order level, and then distribute that across the associated parcels.

The key was ensuring the level of aggregation matched what each question was asking.

What is a left join?

Left join retains all rows from the left table, including those without a match

Give me a 60-second overview of how you approached the take-home task.

I identified which questions required order-level versus parcel-level data, joined the relevant tables, and calculated the requested metrics. I paid particular attention to multiple shipments and refunds per order because these could inflate results. I used distinct counts, existence checks, and pre-aggregation as appropriate, then interpreted the results and their limitations.

What was the most important data-granularity challenge?

Orders, shipments, and refunds exist at different levels. One order may contain several parcels and multiple refund records. A direct join can multiply rows and lead to inflated order values or incorrect counts. I needed to choose the correct unit of analysis for each question. For example, 2 shipments joined with 3 refunds for the same order can produce 6 rows.

What did you find for courier volume, average delivery time, and regional speed?

Bolt handled the most parcels. Delivered parcels took an average of 2.596 days to ship. North had the highest average shipping time. These are descriptive findings rather than proof that a courier causes delays.

Which product category had the highest total order value, and what does that measure?

Electronics. Total order value is the sum of the relevant orders' values; it does not automatically equal net revenue, contribution margin, or profit. Refunds and costs would need separate treatment.

How did you calculate the refund rate by product category?

I divided the number of distinct orders with at least one refund by the number of distinct orders in the category, multiplied by 100. I used a LEFT JOIN to retain orders without refunds and COUNT(DISTINCT) to avoid counting orders multiple times if they had multiple refund records.

What were the highest and lowest observed refund rates?

Clothing was highest at 9.06

, followed by Toys at 8.89

, Electronics at 8.14

, Sports at 7.31

, and Home lowest at 7.07

. The Clothing–Home difference was approximately 1.99 percentage points.

How did you test whether Clothing and Home refund rates differed?

I used a two-sided two-proportion z-test. H0: both category refund probabilities are equal. H1: they differ. Clothing had 91 refunded orders in 1,004 orders; Home had 91 in 1,288. Using the pooled proportion under H0 gave a z-statistic of approximately 1.76 and a two-sided p-value of approximately 0.079. This is not statistically significant at the 5

level.

How did you calculate net revenue per parcel in Question 8?

First, I summed refunds by order and subtracted them from order value. Next, I counted parcels belonging to each order. I then divided the order's net revenue equally among its parcels and assigned these parcel-level amounts to their couriers and categories. Finally, I grouped by courier and category and calculated average allocated net revenue per parcel.

What was the leading net-revenue-per-parcel combination?

Arrow–Electronics at £99.46 per parcel, narrowly ahead of Bolt–Electronics at £99.25 and Cyclone–Electronics at £98.05. This is an observed revenue ranking under my allocation method, not necessarily a profitability or causal performance ranking. The Arrow–Bolt difference is only £0.21 per parcel.

What did you assume when splitting an order's net revenue across parcels?

I assumed every parcel within an order received an equal share of that order's net revenue. This was a practical allocation rule because I did not have sufficiently granular parcel-specific sales and refund values. If parcels contain differently priced items, the allocation can misstate true parcel-level economics.

What is the difference between LEFT JOIN and INNER JOIN in your refund-rate query?

A LEFT JOIN starting from orders keeps every order, including orders with no refund match. An INNER JOIN keeps only orders with matching refunds. For the refund-rate denominator, INNER JOIN would wrongly exclude non-refunded orders. Either join can produce multiple rows if an order has multiple refunds. LEFT JOIN does not prevent duplicate rows.

Why COUNT(DISTINCT order_id) instead of COUNT(*)?

COUNT(*) counts joined rows, not necessarily unique orders. If an order has several refund records, a join can repeat that order. COUNT(DISTINCT order_id) ensures each order is counted once when measuring the proportion of orders refunded.

What does EXISTS do in Question 7?

EXISTS checks whether at least one matching refund row exists for a shipment's order. It returns a TRUE or FALSE condition rather than additional joined rows, so it avoids multiplying the shipment count when an order has multiple refunds. The shipment must also have status Returned.

Why did you use CTEs in the net-revenue calculation?

CTEs, or Common Table Expressions, let me break the query into understandable intermediate stages. In Question 8, I aggregated refunds by order before combining them with order values, then calculated parcel counts and allocated revenue. This makes the logic easier to verify and reduces the risk of double-counting. A CTE is a way to organise a query, not a type of join.

Why can't you conclude that Arrow is the most profitable courier?

I calculated net revenue per parcel, not courier profit. I lacked courier costs, delivery prices, claims costs, and other expense information. The couriers may also handle different products, destinations, and customer profiles, so observed results can reflect shipment mix rather than courier quality.

What is the biggest statistical limitation of your refund-rate comparison?

The observed 1.99-percentage-point gap is not statistically significant at the conventional 5

threshold. Furthermore, I selected the highest and lowest categories from several categories, making a multiple-comparisons concern relevant. Even a statistically significant gap would not establish what causes it.

If you could improve the analysis with more data, what would you request?

I would seek parcel-level item values and refunds, courier delivery charges, shipment characteristics, delivery dates, customer and regional mix, and refund reasons. These would improve revenue allocation, permit profitability comparisons, and help distinguish courier performance from differences in assigned shipments.

How would you validate your SQL results before relying on them?

I would check row counts before and after joins, confirm that aggregation is at the intended order or shipment level, examine NULLs and duplicate refund records, manually reconcile sample orders, and run basic reasonableness checks, such as ensuring that category rates and revenue totals are consistent with their definitions.

Courier A delivers in 2.4 days on average; Courier B takes 2.8 days. Would you move all shipments to A?

Not immediately. I'd compare shipment mix, regions, service levels, prices, and the distribution of delivery times, not only their averages. I would consider a controlled reallocation or pilot and evaluate delivery speed, cost, and reliability before scaling.

A courier's returned-and-refunded rate rises from 2% to 3%. How big is the change?

The increase is 1 percentage point, equivalent to a 50% relative increase. I'd check sample size, mix, and the cost associated with returns before assessing the commercial impact.

Carrier A costs £3.00 per parcel and has a 5% failure rate. Carrier B costs £3.30 and has a 2% failure rate. Each failure costs £15. Which is cheaper overall?

Expected cost per parcel for A is £3.00 + 0.05 × £15 = £3.75. For B it is £3.30 + 0.02 × £15 = £3.60. B is cheaper by £0.15 per parcel on these assumptions, despite its higher upfront charge.

The interviewer gives you ambiguous numerical data. What should you do first?

Clarify the objective, denominator, definitions, and assumptions before calculating. Then structure the problem, make the arithmetic transparent, sanity-check the result, and give a recommendation with a clear caveat where uncertainty remains.