SQL query to count number of payments made in a time period with data from two different table

join;select

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

Best Answer

Here is a query that should give you what you are looking for. This is Oracle SQL syntax, but may work in other databases as well.

SELECT dau.datetime, count(*) FROM DAU
WHERE EXISTS (select 1 from Payer 
   WHERE Payer.user_id=DAU.user_id 
   AND Payer.payment_timestamp BETWEEN trunc(dau.datetime)-90 AND trunc(DAU.datetime))
GROUP BY dau.datetime
ORDER BY DateTime;

Data to demonstrate:

create table Payer as (select 1001 user_id, to_date('06/01/2015 07:00','MM/DD/YYYY HH24:MI') payment_timestamp from dual);
insert into Payer values (1001, to_date('06/01/2015 07:30','MM/DD/YYYY HH24:MI'));
insert into Payer values (1002, to_date('06/01/2015 10:00','MM/DD/YYYY HH24:MI'));
insert into Payer values (1001, to_date('06/02/2015 13:50','MM/DD/YYYY HH24:MI'));
insert into Payer values (1002, to_date('06/02/2015 08:00','MM/DD/YYYY HH24:MI'));
insert into Payer values (1004, to_date('06/02/2015 09:00','MM/DD/YYYY HH24:MI'));
insert into Payer values (1003, to_date('06/03/2015 10:00','MM/DD/YYYY HH24:MI'));
insert into Payer values (1005, to_date('06/03/2015 08:30','MM/DD/YYYY HH24:MI'));

create table DAU as (select 1001 user_id, to_date('06/01/2015','MM/DD/YYYY') datetime from dual);
insert into DAU values (1002, to_date('06/01/2015','MM/DD/YYYY'));
insert into DAU values (1003, to_date('06/01/2015','MM/DD/YYYY'));
insert into DAU values (1001, to_date('06/02/2015','MM/DD/YYYY'));
insert into DAU values (1002, to_date('06/02/2015','MM/DD/YYYY'));
insert into DAU values (1004, to_date('06/02/2015','MM/DD/YYYY'));
insert into DAU values (1002, to_date('06/03/2015','MM/DD/YYYY'));
insert into DAU values (1003, to_date('06/03/2015','MM/DD/YYYY'));
insert into DAU values (1005, to_date('06/03/2015','MM/DD/YYYY'));
Related Question