How much money did we really earn, and from whom?

What you need to know first

Review these core concepts before starting. If any concept is new, use the lesson link to learn the full theory.

Step 1: Decide what counts as revenue

Why this step. If we add up orders that were never paid, we invent money. So first we look at the order statuses to decide what counts as real income.

Before adding any money, look at what values the order_status column holds. An order moves through stages after the customer clicks buy.

'paid' means the customer's money really arrived. 'cancelled' means the order was dropped before it was sent. 'pending' means the customer started the payment but it has not finished. 'refunded' means the customer returned the item and got the money back.

Meera groups the 180 orders by order_status. For each status she counts the orders and adds up the money, and she puts the biggest group first.

Run the query to see the status breakdown.

SELECT order_status, COUNT(*) AS orders, ROUND(SUM(order_total), 2) AS value
FROM orders
GROUP BY order_status
ORDER BY orders DESC, order_status

order_status | orders | value
-------------+--------+---------
paid         | 93     | 43606.63
cancelled    | 30     | 11248.61
pending      | 29     | 14478.51
refunded     | 28     | 14082.4

mix = (df_orders.groupby("order_status")
       .agg(orders=("order_id", "count"), value=("order_total", "sum"))
       .round(2).reset_index()
       .sort_values(["orders", "order_status"], ascending=[False, True]))
print(mix.to_string(index=False))

order_status  orders    value
        paid      93 43606.63
   cancelled      30 11248.61
     pending      29 14478.51
    refunded      28 14082.40

Watch out. Check for spelling and spacing differences in the status text, such as 'Paid ' with a space or 'PAID' in capitals. If the data is messy, use LOWER(TRIM(order_status)) to clean it first.

What it tells us. Of the 180 orders, exactly 93 are 'paid', worth ₹43,606.63 of real money. The other 87 orders did not bring in money: 30 were 'cancelled' (₹11,248.61), 29 are 'pending' (₹14,478.51), and 28 were 'refunded' (₹14,082.40). Together these are ₹39,809.52. So the real revenue is ₹43,606.63, not ₹83,416.15.

Step 2: Wrong turn: Summing all raw orders without status filtering

Why this step. To see why the accountant's number was wrong, we repeat the mistake on purpose and look at what it gives.

Meera now runs the same query the accountant ran. It is a plain SUM(order_total) over every row, with no WHERE filter.

Many beginners do this, because they treat a database table like an Excel sheet where every row is final. But an orders table records everything that happened, including orders that failed.

Run the query to see the wrong total.

SELECT COUNT(*) AS total_orders, ROUND(SUM(order_total), 2) AS raw_revenue
FROM orders

total_orders | raw_revenue
-------------+------------
180          | 83416.15

total_orders = len(df_orders)
raw_revenue = df_orders["order_total"].sum().round(2)
print(f"Total orders: {total_orders}, Raw total: ₹{raw_revenue}")
print("Flaw: Naive summation includes cancelled, pending, and refunded orders, overstating true revenue by 91.3%.")

Total orders: 180, Raw total: ₹83416.15
Flaw: Naive summation includes cancelled, pending, and refunded orders, overstating true revenue by 91.3%.

Watch out. When you show a total, always say what you counted. 'All orders placed' and 'money actually received' are two different numbers, and people mix them up.

What it tells us. The query says 180 orders and ₹83,416.15. It counts every row as money received, so it is 91.3 percent too high. That is ₹39,809.52 of money that does not exist. Showing this number to investors would be a serious mistake. Real revenue must use only order_status = 'paid'.

Step 3: Revenue by week and identifying partial-week artifacts

Why this step. One total for the whole month hides what is happening inside it. Looking week by week shows how sales are moving, and also shows where the data is incomplete.

Now that real revenue means 'paid' orders, Meera looks at how sales change over time. Day by day is too jumpy, because weekends are different from weekdays. One total for the month hides everything in between. A week is a good size.

She puts each of the 93 paid orders into the week it belongs to, using the Monday that starts that week. In SQL, DATE_TRUNC('week', created_at)::DATE gives that Monday. In Pandas, she turns created_at into a date and uses .dt.to_period('W').dt.start_time.dt.date.

For each week she counts the orders and adds up the money, in date order.

Run the weekly query.

SELECT DATE_TRUNC('week', created_at)::DATE AS week_start,
       COUNT(*) AS orders,
       ROUND(SUM(order_total), 2) AS revenue
FROM orders
WHERE order_status = 'paid'
GROUP BY 1
ORDER BY 1

week_start | orders | revenue
-----------+--------+---------
2026-07-27 | 4      | 2389.71
2026-08-03 | 31     | 18887.54
2026-08-10 | 38     | 14958.13
2026-08-17 | 20     | 7371.25

paid = df_orders[df_orders["order_status"] == "paid"].copy()
paid["week_start"] = pd.to_datetime(paid["created_at"]).dt.to_period("W").dt.start_time.dt.date
weekly = (paid.groupby("week_start")
          .agg(orders=("order_id", "count"), revenue=("order_total", "sum"))
          .round(2).reset_index())
print(weekly.to_string(index=False))

week_start  orders  revenue
2026-07-27       4  2389.71
2026-08-03      31 18887.54
2026-08-10      38 14958.13
2026-08-17      20  7371.25

Watch out. Always say which weeks are incomplete. If the bosses do not know Week 1 has only 2 days, they may think the business crashed at the start and end of the month.

What it tells us. Week 1 (starting Monday 2026-07-27) has 4 orders worth ₹2,389.71. Week 2 (2026-08-03) has 31 orders worth ₹18,887.54. Week 3 (2026-08-10) has 38 orders worth ₹14,958.13. Week 4 (2026-08-17) has 20 orders worth ₹7,371.25. Week 1 and Week 4 look small only because the data runs from August 1 to August 19, so Week 1 has just two days and Week 4 has just three days. Compare the two full weeks, Week 2 and Week 3. Orders went up by 22.6 percent (from 31 to 38), but money went down by 20.8 percent (from ₹18,887 to ₹14,958). So each order is worth less than before.

Step 4: Who are the biggest customers? Evaluating revenue concentration

Why this step. If a few customers pay for most of the sales, losing one of them could hurt a lot. So we check how much each top customer adds.

Some shops get a big part of their money from one or two big buyers, for example a company that buys many gifts at once. If that buyer leaves, sales fall sharply. So it is worth checking.

Meera groups the paid orders by user_id. For each customer she finds the number of orders, the total money, and the share of all sales. To get the share in one query, she uses a window function: ROUND(100.0 * revenue / SUM(revenue) OVER (), 1). In Pandas, she divides each customer's revenue by by_user['revenue'].sum().

She puts the biggest spenders first and keeps the top 5.

Run the customer query.

WITH by_user AS (
  SELECT user_id, COUNT(*) AS orders, SUM(order_total) AS revenue
  FROM orders
  WHERE order_status = 'paid'
  GROUP BY user_id
)
SELECT user_id, orders, ROUND(revenue, 2) AS revenue,
       ROUND(100 * revenue / SUM(revenue) OVER (), 1) AS pct_of_total
FROM by_user
ORDER BY revenue DESC, user_id
LIMIT 5

user_id | orders | revenue | pct_of_total
--------+--------+---------+-------------
U030    | 4      | 3064.8  | 7
U051    | 3      | 1970.88 | 4.5
U069    | 2      | 1854.56 | 4.3
U055    | 2      | 1647.32 | 3.8
U044    | 3      | 1543.5  | 3.5

by_user = paid.groupby("user_id").agg(orders=("order_id", "count"), revenue=("order_total", "sum")).reset_index()
by_user["pct_of_total"] = (100 * by_user["revenue"] / by_user["revenue"].sum()).round(1)
by_user["revenue"] = by_user["revenue"].round(2)
top = by_user.sort_values(["revenue", "user_id"], ascending=[False, True]).head(5)
print(top.to_string(index=False))

user_id  orders  revenue  pct_of_total
   U030       4  3064.80           7.0
   U051       3  1970.88           4.5
   U069       2  1854.56           4.3
   U055       2  1647.32           3.8
   U044       3  1543.50           3.5

Watch out. When you use LIMIT, add a second sort column such as user_id. If two customers have the same total, the order can change between runs, and your report will show different names each time.

What it tells us. Customer U030 is the biggest, with 4 paid orders worth ₹3,064.80, which is 7.0 percent of all sales. U051 is second with 3 orders worth ₹1,970.88 (4.5%). U069 is third with 2 orders worth ₹1,854.56 (4.3%). U055 is fourth with 2 orders worth ₹1,647.32 (3.8%). U044 is fifth with 3 orders worth ₹1,543.50 (3.5%). Together the top 5 customers bring in only 23.1 percent of sales. So the money is spread out, and the shop does not depend on a few customers.

Step 5: Do people come back? Measuring repeat purchase loyalty

Why this step. Finding a new customer costs money. A shop does well only if people come back and buy again.

The last question is about loyalty. Shops pay for ads to bring in new shoppers. If every shopper buys once and never returns, the ad cost eats up the profit.

Meera asks: out of the customers who paid at least once, how many paid two or more times? She counts paid orders per user_id. Then she counts the users with more than one paid order, using SUM(CASE WHEN paid_orders > 1 THEN 1 ELSE 0 END), and divides by COUNT(*), the number of buyers.

In Pandas, she keeps the users with paid_orders > 1 and divides their count by the total number of buyers.

Run the repeat customer query.

WITH paid_orders AS (
  SELECT user_id, COUNT(*) AS paid_orders
  FROM orders
  WHERE order_status = 'paid'
  GROUP BY user_id
)
SELECT COUNT(*) AS buyers,
       SUM(CASE WHEN paid_orders > 1 THEN 1 ELSE 0 END) AS repeat_buyers,
       ROUND(100.0 * SUM(CASE WHEN paid_orders > 1 THEN 1 ELSE 0 END) / COUNT(*), 1) AS repeat_pct
FROM paid_orders

buyers | repeat_buyers | repeat_pct
-------+---------------+-----------
50     | 29            | 58

cust_orders = paid.groupby("user_id").size()
buyers = len(cust_orders)
repeat_buyers = int((cust_orders > 1).sum())
repeat_pct = round(100.0 * repeat_buyers / buyers, 1)
print(f"buyers: {buyers} repeat_buyers: {repeat_buyers} repeat_pct: {repeat_pct}")

buyers: 50 repeat_buyers: 29 repeat_pct: 58.0

Watch out. Only count customers who paid at least once (the 50 buyers). If you also count people who only had cancelled or pending orders, the repeat rate will look lower than it really is.

What it tells us. There are 50 customers who paid at least once in these 19 days. Of them, 29 paid more than once. That is a repeat rate of 58.0 percent. More than half of the buyers came back in under three weeks. That is a good sign for investors.

Recommendation message to stakeholder

The analysis is only useful once someone can act on it. This is the exact message sent to the stakeholder with findings, evidence, decision, risk, and next steps.

Practise the same moves

• SQL aggregations
• Window functions
• Trends and partial weeks