Problem, given the table definitions below write a single SQL query that fulfils the following business requirement.
Calculate Member Lifecycle Status
In a given month each member is considered a certain lifecycle type. This will change on monthly basis based on their previous & current month's activity, i.e.
New = New P2P in the calendar month
Existing i.e. P2P in a prior calendar month can be either:
Retained = Active in the prior calendar month and the current calendar month
Unretained = Active in the prior calendar month, but not active in the current calendar month
Reactivated = No activity in the prior calendar month, but active in the current calendar month
Lapsed = No activity in the prior calendar month or the current calendar month
P2P stands for “Pay to Play”, i.e. the Member has placed at least one real money wager since joining.
Active means the member has made a minimum of one real money wager in the month.
The “REVENUE_ANALYSIS” table provides a summary of each member’s activity on a given date, listing the total amounts wagered and won for each game played that day. If a member does not play on a given date there will be no entries in the table. You can assume this is a very large table ~100M rows.
There is also a CALENDAR table that provides a base “date” dimension, one row per day from 2000 to 2020.
ACTIVITY_DATE DATE NOT NULL, Date wager was made
MEMBER_ID INTEGER NOT NULL, Unique Player identifier
GAME_ID SMALLINT NOT NULL, Unique Game identifier
WAGER_AMOUNT REAL NOT NULL, Total amount wagered on the game
NUMBER_OF_WAGERS INTEGER NOT NULL, Number of wagers on the game
WIN_AMOUNT REAL NOT NULL, Total amount won on the game
ACTIVITY_YEAR_MONTH INTEGER NOT NULL, YYYYMM
BANK_TYPE_ID SMALLINT DEFAULT 0 NOT NULL 0=Real money, 1=Bonus money
CALENDAR_DATE DATE NOT NULL, Base date (YYYY-MM-DD)
CALENDAR_YEAR INTEGER NOT NULL, 2010, 2011 etc
CALENDAR_MONTH_NUMBER INTEGER NOT NULL, 1-12
CALENDAR_MONTH_NAME VARCHAR(100), January, February etc
CALENDAR_DAY_OF_MONTH INTEGER NOT NULL, 1-31
CALENDAR_DAY_OF_WEEK INTEGER NOT NULL, 1-7
CALENDAR_DAY_NAME VARCHAR(100), Monday, Tuesday etc
CALENDAR_YEAR_MONTH INTEGER NOT NULL, 201011, 201012, 201101 etc
The required solution is a view with the following columns:
Listing one row per Member per month, starting from the month in which they first place a real money wager, giving their lifecycle status for that month, also if the member has lapsed we need a rolling count of the number of months since they were last active.