Customer Purchase Frequency: Segmenting Loyalty Using 90-Day Aggregations

Business Context

PulseRetail is a growing mid-market retail chain with 55 stores across the US and a registered customer base of over 180,000 loyalty card members. The company runs a tiered loyalty program — Bronze, Silver, and Gold — based on purchase frequency. With the loyalty program renewal cycle approaching, the CRM team needs to identify how often each registered customer has made a purchase in the last 90 days (rolling window from today). This analysis will directly feed into the next email campaign: high-frequency customers will receive a Gold upgrade offer, moderate buyers will get a re-engagement incentive, and low-frequency or lapsed customers will receive a win-back discount.

The SQL Challenge

The core skill here is COUNT() aggregation combined with strict date filtering using WHERE sale_date >= CURRENT_DATE - INTERVAL '90 days'. You will group transactions by customer to compute the number of distinct purchase visits, total units bought, and total spend per customer over the 90-day window. A key distinction here is using COUNT(DISTINCT order_id) vs COUNT(sale_id) — since a single customer visit may produce multiple line-item rows, counting distinct orders gives accurate visit frequency rather than line count. Supporting skills include GROUP BY on customer dimensions, ROUND() for clean currency output, and HAVING to optionally filter out customers with zero activity.

Your Role and Deliverable

You are the data analyst supporting the CRM and loyalty team. Your task is to write a SQL query that returns one row per customer showing their purchase frequency (number of distinct orders), total units purchased, total spend, and average order value — all within the last 90 days. The output will be handed to the CRM team to apply loyalty tier logic and build the campaign audience segments.