Luba Belokon walks through some sample customer retention analysis queries written in SQL:
Customer retention curves are essential to any business looking to understand its clients and will go a long way towards explaining other things like sales figures or the impact of marketing initiatives. They are an easy way to visualize a key interaction between customers and the business, which is to say, whether or not customers return — and at what rate — after the first visit.
The first step to building a customer retention curve is to identify those who visited your business during the reference period, what I will call p1. It is important that the length of the period chosen is a reasonable one, and reflects the expected frequency of visits.
Different types of businesses are going to expect their customers to return at different rates:
A coffee shop may choose to use an expected frequency of visits of once a week.
A supermarket may choose a longer period, perhaps two weeks or one month.
In this case, I think the motivation portion is better than the queries themselves, but the article definitely works as an inspiration for building out good measures of frequency of occurrence.