Posts: 30
Registered: ‎05-18-2017

Revenue analysis

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.


Source Tables

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.

New Contributor
Posts: 8
Registered: ‎10-31-2017

Re: Revenue analysis

 You gotta do your own homework... :)