Customer Product Adoption Analysis: Counting Products Per Customer

Business Context

Meridian Retail Bank is a full-service consumer bank operating across 14 branches in the Midwest, serving approximately 38,000 active customers. The bank offers six core retail products: Savings Account, Checking Account, Credit Card, Personal Loan, Fixed Deposit, and Home Loan. Meridian's Growth & Retention team tracks a metric called Product Depth — the average number of products held per customer — as a leading indicator of customer loyalty and lifetime value. Internal research has shown that customers holding 3 or more products have a 4.2× lower churn rate than single-product holders.

For the current quarter (Q4 2024), the Head of Retail Banking has asked the analytics team for a product adoption snapshot: how many customers hold exactly one product, two products, three products, and so on. This breakdown will inform the upcoming cross-sell campaign — specifically, which product-count tier to target and with which product offer.

The SQL Challenge

The bank's CRM database stores one row per customer–product relationship in an account_products table. There is no pre-aggregated "product count" column — you will need to use GROUP BY on customer_id to count how many distinct products each customer holds, then apply a second layer of GROUP BY to bucket customers by their product count. A JOIN to the customers table ensures only active, non-churned customers are included. Date filtering on enrollment_date scopes the snapshot to accounts opened on or before 31 December 2024.

Your Role and Deliverable

You are the Junior Data Analyst on Meridian's Growth & Retention team. Your manager has asked for the product adoption distribution before the cross-sell planning meeting on Friday. Write a SQL query that returns one row per product-count tier, showing products_held (the number of products), customer_count (how many customers are in that tier), and pct_of_customers (that tier's share of the active customer base). Order results from fewest to most products held.