Purchasing Activity by Product Type

This is the same question as problem #4 in the SQL Chapter of Ace the Data Science Interview!

Assume you are given the table below for purchasing activity by product type. Write a query to calculate the cumulative purchases for each product type over time in chronological order.

Output the order date, product, and the cumulative number of quantities purchased (conveniently abbreviated as ).

Column Name | Type |
---|---|

order_id | integer |

product_type | string |

quantity | integer |

order_date | datetime |

order_id | product_type | quantity | order_date |
---|---|---|---|

213824 | printer | 20 | 06/27/2022 12:00:00 |

212312 | hair dryer | 5 | 06/28/2022 12:00:00 |

132842 | printer | 18 | 06/28/2022 12:00:00 |

284730 | standing lamp | 8 | 07/05/2022 12:00:00 |

order_date | product_type | cum_purchased |
---|---|---|

06/27/2022 12:00:00 | printer | 20 |

06/28/2022 12:00:00 | hair dryer | 5 |

06/28/2022 12:00:00 | printer | 38 |

07/05/2022 12:00:00 | standing lamp | 8 |

Over the course of June 27, 2022, 20 printers were purchased.

Over the course of June 28, 2022, 5 hair dryers were purchased.

**Goal:** Find the cumulative purchases for each product type chronologically.

That means the number of purchases of each product type should be summed chronologically by the order date. There's a handy window function that can be very useful for this case - it's the window function!

*Note that you do not need the field in the SELECT clause and do not need to order your solution in any specific order.*

Showing the output for printer only:

order_date | product_type | quantity | cum_purchased |
---|---|---|---|

06/27/2022 12:00:00 | printer | 20 | 20 |

06/28/2022 12:00:00 | printer | 18 | 38 |

07/05/2022 12:00:00 | printer | 25 | 63 |

09/16/2022 12:00:00 | printer | 15 | 78 |

09/26/2022 12:00:00 | printer | 12 | 90 |

Let us explain to you how the window function works.

- On 06/27/2022, the cumulative purchase is equivalent to the quantity of 20 as this is the first order.
- Subsequently on 06/28/2022, there is an order of 18 printers and the cumulative purchase is 38 printers which are 20 + 18.
- Then, on 07/05/2022, the cumulative purchases are 63 which is 20 + 18 + 25.

Do you get the gist? Using this function, we can obtain the cumulative purchases very easily.

PostgreSQL 14