Analyzing Order History

Analyzing BookNest's Order History with Spark SQL

BookNest's quarterly review needs one table: orders, delivered revenue, return rate, new customers and repeat share. One statement does it with CTEs, a self-join to each customer's first order and conditional aggregates.

BookNest's quarterly KPIs in one Spark SQL query

kpis = spark.sql("""
WITH firsts AS (
  SELECT customer_id, min(order_ts) AS first_ts FROM orders GROUP BY customer_id),
tagged AS (
  SELECT o.*, concat(year(o.order_ts), '-Q', quarter(o.order_ts)) AS qtr,
         o.order_ts = f.first_ts AS is_first
  FROM orders o JOIN firsts f USING (customer_id))
SELECT qtr,
       count(*) AS orders,
       sum(total) FILTER (WHERE status = 'delivered') AS revenue,
       round(100 * count_if(status = 'returned')
             / count_if(status IN ('delivered', 'returned')), 2) AS return_pct,
       count_if(is_first) AS new_customers,
       round(100 * count_if(NOT is_first) / count(*), 1) AS repeat_pct
FROM tagged GROUP BY qtr ORDER BY qtr""")
kpis.show()
kpis.write.mode("overwrite").parquet("out/quarterly_kpis")          # for the dashboard

Output

+-------+------+----------+----------+-------------+----------+
|    qtr|orders|   revenue|return_pct|new_customers|repeat_pct|
+-------+------+----------+----------+-------------+----------+
|2025-Q1|165005|5199724.32|      4.23|        44169|      73.2|
|2025-Q2|166569|5241233.56|      4.28|         4838|      97.1|
|2025-Q3|168210|5291176.09|      4.21|          774|      99.5|
|2025-Q4|168133|5289649.98|      4.19|          171|      99.9|
|2026-Q1|165542|5124967.59|      4.31|           34|     100.0|
|2026-Q2|166541|4833531.83|      3.62|           12|     100.0|
+-------+------+----------+----------+-------------+----------+

In the sample data nearly all customers first order in the first two quarters. The low 2026-Q2 return rate is not good news but right-censoring: returns arrive up to 20 days after delivery, so June's orders have not had time to come back. The 50,000-row firsts side is broadcast.

MENU