#182

Consecutive Months Orders

MEDIUMTime-series & PartitionsPOSTGRESQL

Problem Description

An e-commerce loyalty analytics pipeline identifies customers with consecutive-month ordering behavior — a key signal for loyalty tier classification. A customer qualifies if they placed at least one order in each of at least **3 consecutive calendar months**. Given the `customers` and `orders` tables, find all qualifying customers. Return `customerid`, `customername`, `consecutive_months_start` (the first month of the qualifying streak in `YYYY-MM` format), and `consecutive_months_count` (the length of the streak). Order by `consecutive_months_start` ascending, then `customerid` ascending.

Topics

window functionsaggregationjoinsdate functions

Asked at Companies

amazon

Solve This Problem

Sign up to access the interactive code editor, run test cases, view the editorial, and get AI-powered feedback on your solution.

Start Solving →