Could someone please tell me how to write sql query for the following question? I am not able to get how to write the condition where it selects those user_id which is present on the said date and also made a payment in the last 90 days. The question is as follows:
Every day a player comes in the game, his/her user_id and date when he/she entered the game is recorded in a table say Table DAU. It has two columns. 1. user_id 2. date
On a particular date, for a player there can be only one entry (No multiple entries on same day). However, the same player can come on different dates.
This table has data from 1/1/2015 (i.e January 1st) onwards. Below are sample entries from this table.
user_id date
1001 6/1/2015
1002 6/1/2015
1003 6/1/2015
1001 6/2/2015
1002 6/2/2015
1004 6/2/2015
1002 6/3/2015
1003 6/3/2015
1005 6/3/2015
Table Payment
Also, every time a player makes a payment in the game, his/her user_id and timestamp when he/she made the payment is recorded in a table, say Table Payment. It has two columns. 1. user_id 2. payment_timestamp
A player can make multiple payments on the same day and across days also.
Below are sample entries from this table.
user_id payment_timestamp
1001 6/1/2015 07:00
1001 6/1/2015 07:30
1002 6/1/2015 10:00
1001 6/2/2015 13:50
1002 6/2/2015 08:00
1004 6/2/2015 09:00
1003 6/3/2015 10:00
1005 6/3/2015 08:30
We define 90-Day Payer DAU for a particular date, say 6/1/2015 as follow:
Count (Player who came on 6/1/2015 and has made at least one payment in the game in last 90 days i.e from 3/4/2015 to 6/1/2015)
Similarly, 90-Day Payer DAU for 6/2/2015 will be:
Count (Players who came on 6/2/2015 and have made at least one payment in the game in last 90 days i.e from 3/5/2015 to 6/2/2015)
Question:
Using the DAU Table and Payment table, please write a SQL query to produce the below table.
Date 90_Day_Payer DAU
6/1/2015
6/2/2015
6/3/2015
6/4/2015
6/5/2015
6/6/2015
6/7/2015