1. Lighthouse

Level 4 · Are most orders small?

The request

From Laura Bennett, Head of Finance

“Our average order in 2025 was over $500, but that doesn't feel like a typical order to me. Can you show me how order values are spread out, up to $2,000?”

Your goal

Take order lines with quantity > 0 from orders with status 'completed' placed in 2025, and compute each order's value (the sum of quantity * unit_price * (1 - discount_pct / 100)). Draw a histogram with bins every $100 from 0 to 2,000. Set result to a DataFrame with the same counts: columns bin_start (0, 100, …, 1900) and n_orders (orders whose value is in [bin_start, bin_start + 100); the last bin also includes exactly 2,000), 20 rows in bin order, index 0 to 19. Orders above $2,000 are left out.

  • Use the orders DataFramenot done yet
  • Use the order_items DataFramenot done yet
  • Show exactly bin_start n_ordersnot done yet

Your code

Python

Loading the editor…

    ResultPreview of orders · press Run to see yours
    Preview of orders
    order_idcustomer_idorder_datestatuschannel
    10011252023-01-01 20:51:19completedweb
    10021562023-01-02 07:58:46completedweb
    1003222023-01-02 10:25:27completedmarketplace
    1004482023-01-02 11:21:51completedmarketplace

    Mote checks your result

    Press Run. Each row that matches the goal becomes a firefly in your lantern.

    DataFrames

    Loaded for you as pandas DataFrames, plus pd and np.

    orders12,000 rows · 7 columns
    • order_idinteger
    • customer_idinteger
    • order_datetimestamp
    • statusvarchar
    • channelvarchar
    • shipping_feedecimal
    • coupon_codevarchar
    First 5 rows of orders
    order_idcustomer_idorder_datestatuschannelshipping_feecoupon_code
    10011252023-01-01 20:51:19completedweb0.00NaN
    10021562023-01-02 07:58:46completedweb0.00NaN
    1003222023-01-02 10:25:27completedmarketplace4.99WELCOME10
    1004482023-01-02 11:21:51completedmarketplace4.99WELCOME10
    1005822023-01-02 11:43:36completedweb0.00WELCOME10
    order_items30,338 rows · 6 columns
    • order_item_idinteger
    • order_idinteger
    • product_idinteger
    • quantityinteger
    • unit_pricedecimal
    • discount_pctdecimal
    First 5 rows of order_items
    order_item_idorder_idproduct_idquantityunit_pricediscount_pct
    11001121931.990.00
    21001154311.990.00
    31002154411.990.00
    410021941149.000.00
    51002193219.990.00
    customers2,018 rows · 9 columns
    • customer_idinteger
    • first_namevarchar
    • last_namevarchar
    • emailvarchar
    • cityvarchar
    • statevarchar
    • signup_datedate
    • segmentvarchar
    • referral_sourcevarchar
    First 5 rows of customers
    customer_idfirst_namelast_nameemailcitystatesignup_datesegmentreferral_source
    1OwenHudsonowen.hudson@icloud.comSan FranciscoCA2022-06-02consumersearch
    2DouglasMendozadmendoza69@gmail.comAtlantaGA2022-06-02consumerNaN
    3HenryBrookshbrooks@lakesidecafe.comLos AngelesCA2022-06-04businessNaN
    4BrianGrahambgraham@bluecedarhomes.comSeattleWA2022-06-06businesspinterest
    5KyleJimenezkjimenez27@yahoo.comIndianapolisIN2022-06-07consumerNaN
    products120 rows · 8 columns
    • product_idinteger
    • namevarchar
    • categoryvarchar
    • subcategoryvarchar
    • unit_pricedecimal
    • unit_costdecimal
    • launched_ondate
    • is_activeboolean
    First 5 rows of products
    product_idnamecategorysubcategoryunit_priceunit_costlaunched_onis_active
    101Vale Velvet LoveseatFurnitureSofas1659.00NaN2023-09-11true
    102Alder Oak Coffee TableFurnitureTables919.00503.502024-05-16true
    103Orchard Rattan Dining ChairFurnitureChairs789.00429.872023-05-27true
    104Upland Oak BookcaseFurnitureStorage429.00214.702022-12-20true
    105Avery Leather Bed FrameFurnitureBeds449.00232.702021-08-31true
    returns1,673 rows · 5 columns
    • return_idinteger
    • order_item_idinteger
    • returned_ondate
    • reasonvarchar
    • refund_amountdecimal
    First 5 rows of returns
    return_idorder_item_idreturned_onreasonrefund_amount
    1142023-01-10damaged83.69
    2422023-01-10wrong_item19.99
    31052023-01-14wrong_item10.99
    4242023-01-16changed_mind26.99
    51262023-01-16late20.99
    employees60 rows · 8 columns
    • employee_idinteger
    • first_namevarchar
    • last_namevarchar
    • departmentvarchar
    • titlevarchar
    • manager_idinteger
    • hire_datedate
    • salaryinteger
    First 5 rows of employees
    employee_idfirst_namelast_namedepartmenttitlemanager_idhire_datesalary
    1CatherineHaleExecutiveChief Executive OfficerNaN2016-03-01285000
    2RobertKimExecutiveChief Operating Officer12017-01-09235000
    3MeganCollinsSalesHead of Sales12018-04-16172000
    4ChloeGutierrezSalesAccount Executive32020-01-2172000
    5FrankBellSalesAccount Executive32023-05-2173000
    web_sessions40,000 rows · 7 columns
    • session_idinteger
    • customer_idinteger
    • started_attimestamp
    • devicevarchar
    • sourcevarchar
    • pages_viewedinteger
    • convertedboolean
    First 5 rows of web_sessions
    session_idcustomer_idstarted_atdevicesourcepages_viewedconverted
    1NaN2023-01-01 23:02:24mobilepaid1false
    2182023-01-01 15:07:49desktoppaid1false
    3NaN2023-01-01 16:55:46desktopdirect7false
    4NaN2023-01-01 12:43:28mobileorganic3false
    5NaN2023-01-01 13:06:21mobilesocial2false
    marketing_spend3,276 rows · 3 columns
    • spend_datedate
    • channelvarchar
    • amountdecimal
    First 5 rows of marketing_spend
    spend_datechannelamount
    2023-01-01paid492.99
    2023-01-01email37.38
    2023-01-01social200.53
    2023-01-02paid709.13
    2023-01-02email64.13

    The lesson

    Bars compare, histograms spread

    A bar chart compares categories. A histogram shows the distribution of one number: it cuts the range of values into intervals, called bins, and draws a bar for how many values fall into each. Neighboring bars touch, because the bins are next to each other on a number line.

    Laura’s question, “is a typical order really $500?”, is about a distribution, so it gets a histogram of order values.

    Drawing it

    First one value per order (the starter does this), then ax.hist:

    fig, ax = plt.subplots()
    ax.hist(order_value, bins=range(0, 2100, 100), edgecolor="white")
    ax.set_xlabel("Order value (USD)")
    ax.set_ylabel("Orders")

    bins takes either a number (bins=30 makes 30 equal bins from the smallest to the largest value) or the list of edges. With edges you control the range and the width. Here 21 edges make 20 bins of $100, from 0 to 2,000. Orders above $2,000, 207 of the 4,295 completed 2025 orders, fall outside the range and are not drawn. Between 20 and 50 bins usually works; with too few you lose the shape, with too many every bar is noise.

    The numbers behind the bars

    np.histogram takes the same arguments and returns what ax.hist draws:

    counts, bin_edges = np.histogram(order_value, bins=range(0, 2100, 100))
    counts[:3]        # [1180, 683, 451]
    bin_edges[:3]     # [0, 100, 200]

    There’s always one more edge than counts. Each bin includes its left edge and excludes its right edge, so an order of exactly $100 counts in the second bin. The last bin is the exception: it includes both ends.

    Mean against median

    This distribution is skewed to the right: lots of small orders and a long tail of big business baskets. In a skewed distribution the mean is pulled toward the tail, while the median stays where most orders are. Mark both with vertical lines and the gap is visible at once:

    ax.axvline(order_value.mean(), color="red", label="Mean")             # $535.90
    ax.axvline(order_value.median(), linestyle="--", label="Median")      # $257.96
    ax.legend()

    Half of 2025’s completed orders were under $258, yet the mean is more than double that. For “what does a typical customer spend”, the median is the honest answer; the mean is still the right number for revenue planning, because revenue is a total.

    Common mistakes

    • Plotting order_items lines instead of whole orders. Group to one value per order first.
    • Leaving bins at the default of 10, which turns this shape into a few blocks.
    • Using a bar chart with gaps between bars for a histogram, which suggests separate categories.
    • Quoting only the mean of a skewed distribution. Give the median next to it.

    Pick what may run on this site. You can change this any time from "Cookie settings" at the bottom of every page.