CODEWITHSUNDEEP
mysqlsql
Marcus Thornton
Marcus Thornton

Reputation: 6193

How to get data of users who created first data within a period of time?

How can I get the amount of money in all wallets for those who created their first wallets in the past 2 months?

I created an SQL fiddle to illustrate the tables. You are welcome to use this to see and run the SQL. My table schema and sample data

The description of table schema and sample data is as below.

I have 2 tables. The first one is called wallet, with columns wallet_id, wallet_owner_id, create_date. We have many wallet owners, and each wallet owner can have multiple wallets. create_date indicates when the wallet was created. Both wallet_id and wallet_owner_id are unique.

e.g.

create table wallet(wallet_id int,wallet_owner_id varchar(20),create_date date);
insert into wallet values
(100000010,'AAAAAAAAAAAAAAA','2017-09-02'),
(100000034,'AAAAAAAAAAAAAAA','2018-06-02'),
(100000132,'BBBBBBBBBBBBBBB','2017-05-03'),
(100001110,'CCCCCCCCCCCCCCC','2018-05-02'),
(100000345,'CCCCCCCCCCCCCCC','2018-05-06');

The second table is called deposit, with columns wallet_owner_id, check_date, amount_in_all_wallets. There is only one row for a wallet owner in a ckeck date

e.g.

create table deposit (amount_in_all_wallets int,wallet_owner_id varchar(20),check_date date);
insert into deposit values
(45,'AAAAAAAAAAAAAAA','2018-06-01'),
(89,'BBBBBBBBBBBBBBB','2018-06-01'),
(97,'CCCCCCCCCCCCCCC','2018-06-01'),
(11,'CCCCCCCCCCCCCCC','2018-06-02');

Because AAAAAAAAAAAAAAA and BBBBBBBBBBBBBBB both created wallets before the past 2 months, I the results should be

amount_in_all_wallets|wallet_owner_id|check_date|first_create_date
97                   |CCCCCCCCCCCCCCC|2018-06-01|2018-05-02
11                   |CCCCCCCCCCCCCCC|2018-06-02|2018-05-02

Query :

SELECT d.amount_in_all_wallets, d.wallet_owner_id, d.check_date, min(d.create_date) as first_create_date
FROM deposit d 
JOIN wallet w ON d.wallet_owner_id = w.wallet_owner_id
WHERE w.create_date > '2018-05-01'

This is not working because I couldn't find a way to incorporate the idea of "created their first wallets in the past 2 months". How can I do that?

Upvotes: 2

Views: 82

Answers (2)

revo
revo

Reputation: 48751

You could add another condition for INNER JOIN that checks for desired dates:

SELECT amount_in_all_wallets,
       w.wallet_owner_id,
       check_date,
       MIN(w.create_date) AS first_create_date
FROM wallet w
JOIN deposit d ON w.wallet_owner_id = d.wallet_owner_id
    AND DATE_SUB(NOW(), INTERVAL 2 MONTH) <=
        (SELECT MIN(create_date)
            FROM wallet
            WHERE wallet_owner_id = w.wallet_owner_id)
GROUP BY amount_in_all_wallets,
         w.wallet_owner_id,
         check_date

Upvotes: 2

Michał Turczyn
Michał Turczyn

Reputation: 37430

You could try this:

select *
from deposit d join (
    select wallet_owner_id, min(create_date) create_date from wallet
    group by wallet_owner_id
    having min(create_date) >= date_add(now(), interval -2 month)  
) w on w.wallet_owner_id = d.wallet_owner_id;

Upvotes: 1

Related Questions