Square's Merchant Analytics team wants to surface merchants whose net revenue is *accelerating* the fastest, so the growth team can prioritize outreach and lending offers. For every merchant, calculate **net monthly revenue** (total `sale` amounts minus total `refund` amounts). Then calculate the **month-over-month growth rate** for each month (as a percentage, relative to the prior month's net revenue). Then calculate a **rolling 3-month average growth rate** for each merchant. A merchant only qualifies if they have at least **3 consecutive month-over-month growth rate values** (i.e., at least 4 months of transaction history), so their most recent rolling average is based on a full 3-value window. Merchants with fewer qualifying months must be excluded entirely. Finally, using each qualifying merchant's **most recent** rolling 3-month average growth rate, rank merchants within their `category` (highest growth first). Output only merchants ranked in the **top 3** within their category. If multiple merchants tie for a rank, they should share that rank (and all tied merchants should be included, even if it means more than 3 rows appear for that category). Output the category, business name, the month of the most recent qualifying rolling average (as a date), the rolling 3-month average growth rate rounded to 2 decimal places, and the category rank. ### Table Schema | Table | Column | Type | |---|---|---| | `merchants` | `merchant_id` | integer | | `merchants` | `business_name` | string | | `merchants` | `category` | string | | `merchants` | `signup_date` | date | | `transactions` | `transaction_id` | integer | | `transactions` | `merchant_id` | integer | | `transactions` | `amount` | decimal | | `transactions` | `transaction_type` | string ('sale' or 'refund') | | `transactions` | `transaction_date` | date | ### Example Input: `merchants` | merchant_id | business_name | category | signup_date | |---|---|---|---| | 1 | Bean There Coffee | Food & Beverage | 2022-01-01 | | 2 | Urban Threads | Retail | 2022-01-01 | | 3 | Fit Studio | Fitness | 2022-01-01 | ### Example Input: `transactions` | transaction_id | merchant_id | amount | transaction_type | transaction_date | |---|---|---|---|---| | 1 | 1 | 1000.00 | sale | 2022-01-15 | | 2 | 1 | 1200.00 | sale | 2022-02-15 | | 3 | 1 | 1500.00 | sale | 2022-03-15 | | 4 | 1 | 1800.00 | sale | 2022-04-15 | | 5 | 1 | 100.00 | refund | 2022-04-20 | | 6 | 2 | 2000.00 | sale | 2022-01-15 | | 7 | 2 | 1800.00 | sale | 2022-02-15 | | 8 | 2 | 2100.00 | sale | 2022-03-15 | | 9 | 2 | 2000.00 | sale | 2022-04-15 | | 10 | 3 | 500.00 | sale | 2022-01-15 | | 11 | 3 | 550.00 | sale | 2022-02-15 | ### Example Output Explanation Bean There Coffee's net revenue is 1000, 1200, 1500, 1700 (1800 - 100 refund) for Jan-Apr, giving growth rates of 20%, 25%, and 13.33%, so the rolling 3-month average at April is 19.44%. Urban Threads' net revenue is 2000, 1800, 2100, 2000, giving growth rates of -10%, 16.67%, -4.76%, so its rolling average at April is 0.64%. Fit Studio only has 2 months of history, so it does not have 3 growth values and is excluded entirely. Since each category has only one qualifying merchant here, both Bean There Coffee and Urban Threads rank #1 in their respective categories.
DROP TABLE IF EXISTS transactions; DROP TABLE IF EXISTS merchants; CREATE TABLE merchants (merchant_id INT, business_name VARCHAR(255), category VARCHAR(100), signup_date DATE); CREATE TABLE transactions (transaction_id INT, merchant_id INT, amount DECIMAL(10,2), transaction_type VARCHAR(10), transaction_date DATE); INSERT INTO merchants VALUES (1, 'Bean There Coffee', 'Food & Beverage', '2022-01-01'), (2, 'Urban Threads', 'Retail', '2022-01-01'), (3, 'Fit Studio', 'Fitness', '2022-01-01'); INSERT INTO transactions VALUES (1, 1, 1000.00, 'sale', '2022-01-15'), (2, 1, 1200.00, 'sale', '2022-02-15'), (3, 1, 1500.00, 'sale', '2022-03-15'), (4, 1, 1800.00, 'sale', '2022-04-15'), (5, 1, 100.00, 'refund', '2022-04-20'), (6, 2, 2000.00, 'sale', '2022-01-15'), (7, 2, 1800.00, 'sale', '2022-02-15'), (8, 2, 2100.00, 'sale', '2022-03-15'), (9, 2, 2000.00, 'sale', '2022-04-15'), (10, 3, 500.00, 'sale', '2022-01-15'), (11, 3, 550.00, 'sale', '2022-02-15');