0

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    
1

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'));

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.