Draft

Calculating FIFO (First-In, First-Out) Gross Margin

1bornForThis
Description
Loading description...
SQL
Databases
View
AllIssues1QuestionsSuggestions1Show Resolved
  • Please sign in or sign up to leave a comment.
  • Twilight_Sun Avatar

    Notes says:

    It is not possible for more units of a certain product to be sold than purchased at any given moment in time because the FIFO Gross Margin calculation assumes that sales are matched to prior purchases.

    but in example tests I see

    edge_case_purchase_data = [
  # sales exceeding purchases: only 2 units purchased, but 3 units sold
  {sku: 'exceed_001', purchase_date: '2023-01-01 10:00', price: 10.00, qty: 2},
...
edge_case_sales_data = [
  # sales exceeding purchases: 3 units sold, but only 2 units purchased
  {sku: 'exceed_001', sale_date: '2023-01-05 10:00', price: 20.00, qty: 3},

    it looks like a contradiction to me. So from the description the result for this sku is unclear (could be 20, 30 or even 40)

    • bornForThis Avatar

      don't have much time today :/. Putting kata into draft and will carefully work with all issues when have time (and re-check my solution because looks that it has issues)...Very interesting problem

    • dfhwze Avatar

      Very interesting, you could easily make an entire series of kata's on this subject, each with different expected behavior for sales when nothing is in stock ;)

    • Twilight_Sun Avatar

      I've also thought there could be at least 2 kata:

      1. easy: this one but without 'wrong' sales
      2. harder: one would need to output a result for every sale; or with some 'wrong' sales, etc

      P.S. as this is quite a practical task (but not in SQL) I know there exists several approaches to calculating a gross margin; not only different ways of dealing with sales of skus that are not in stock but also a different algos, not only FIFO.

    • dfhwze Avatar

      I hope author goes for (2) here, which was the intended way (looking at author comments in a previous discussion). This would be a challenging task in SQL.

    • dfhwze Avatar

      I'm thinking about the following task: one table with current stock and maximum stock capacity for each product, and then the purchases and sales tables. User should make a sql report with current stock after each purchase/sale, and actions taken for each purchase/sale -> approved or rejected. A purchase is rejected when stock is full and a sale is rejected when stock is empty. Do you think such kata is too close to the current kata you are drafting? If not, I'll have a go at authoring such kata.

  • Twilight_Sun Avatar

    purchase / sales - naming is inconsistent: singular/plural

    I'd suggest 'purchases'

  • dfhwze Avatar

    I believe my solution is correct. Could you verify with ref sol?

  • dfhwze Avatar

    The example in description shows purchase: as header, but not sales: for the next set of data

  • dfhwze Avatar

    f there are no corresponding purchases or sales for a SKU, the margin cannot be calculated, and the SKU will not appear in the results.

    Does this mean we only take into account products with records in both tables? In addition, can more units of a certain product be sold than purchased (or still in stock) at any given moment in time?

    • bornForThis Avatar

      updated the Notes section of description as answers to your questions

    • dfhwze Avatar

      It is possible for more units of a certain product to be sold than purchased at any given moment in time.

      In such cases, will there always be future purchases or can there be more total sales than total purchases? And if possible, can the margin still be calculated? I mean, you'll have revenue on an item, without any cost, since you don't have the item, not even in the future

    • dfhwze Avatar

      Also, if more units can be sold than purchased, then Note 1 should not be OK, as you may very well sell X units, having purchased 0. In such case Note 1 should only take into account products with at least 1 sale, regardless of number of units purchased.

    • bornForThis Avatar

      yes, you are absolutely right :/ I just updated initially without thinking deeply. It should not be possible to be sold more than purchased

    • dfhwze Avatar

      ok, then it does not matter if at any certain moment in time, there are more sales than purchases, as future purchases will make up for such eager sales ;) I know enough to start working on this beauty of a kata now

      EDIT: I notice you edited the description that such cases will not occur, which makes it even more clear for the user to solve :)

    • dfhwze Avatar

      Oh I misinterpreted the notes. I thought you would make sure no more than "purchased" amount of units could be "sold" at any given time. Instead, you want us to discard the sales at such moments when no more purchased is currently in stock. That really seems odd tbh. Such sales should not have occured if you ask me.

      If you want to keep the current behavior, I suggest:

      • to add an additional example (edge case) in the description with more sold than purchased at any given time
      • to add a constraint to purchase/sale dates by sku have to be unique ACROSS purchase/sale table, or we wouldn't know how to handle the edge case that a sale and purchase happen at the exact same moment