CODEWITHSUNDEEP
sqlsql-server
Anastasia Kolomina
Anastasia Kolomina

Reputation: 9

How to right SQL query for this:

Please help me with SQL query to get table containing list of clients, sums of their purchases and their phones taking into account the following conditions:

  1. First purchase was not earlier than 2017
  2. Client made more than 3 purchases since 2017

I have the following tables:

1 purchases:

    1.1. purchase_id
    1.2. user_id
    1.3. purchase_date
    1.4. price

2 users:

    2.1. user_id
    2.2. phone

Upvotes: 0

Views: 60

Answers (4)

Gordon Linoff
Gordon Linoff

Reputation: 1269483

I think the simplest query is:

SELECT u.user_id, u.Phone, SUM(Price) AS TotalPrice
FROM users u JOIN
     purchases p
     ON u.user_id = p.user_id
GROUP BY u.user_id, u.phone
HAVING MIN(p.purchase_date) >= '2017-01-01' AND
       COUNT(*) > 3;

The first HAVING condition checks that the earliest purchase date is 2017-01-01 or later. The second checks that there are at least three purchases. Actually, this assumes that users.user_id is unique -- but that is a reasonable assumption.

Upvotes: 0

LukStorms
LukStorms

Reputation: 29647

Join em, and group on the user fields.

And with a HAVING it can filter on the total purchases and a minimum purchase date in 2017 and beyond.

SELECT u.user_id, u.phone, 
 SUM(p.price) AS TotalPurchasePrice
FROM users u
JOIN purchases p ON p.user_id = u.user_id
GROUP BY u.user_id, u.phone
HAVING COUNT(p.purchase_id) > 3 
   AND MIN(p.purchase_date) >= CAST('2017-01-01' AS DATE)

Upvotes: 1

Thom A
Thom A

Reputation: 95554

At a total guess, I suspect that this is actually what you are after, but sample data and expected result would make this a lot easier:

SELECT u.user_id,
       u.Phone,
       SUM(CASE WHEN p.purchase_date >= '20170101' THEN Price END) AS TotalPrice
FROM users u
     JOIN purchases p ON u.user_id = p.user_id
GROUP BY u.user_id,
         u.phone
HAVING MIN(p.purchase_date) >= '20170101'
   AND COUNT(CASE WHEN p.purchase_date >= '20170101' THEN 1 END) > 3;

Upvotes: 1

Winson Tanputraman
Winson Tanputraman

Reputation: 3564

I am not familiar with sql-server, but in typical SQL you can do as follows. Hope this helps.

with query1 as (
    select
        users.user_id,
        min(phone) as phone,
        min(purchase_date) as first_purchase,
        sum(price) netpurchase,
        count(distinct purchase_date) counttxn
    from users
    join purchases
        on users.user_id = purchases.user_id
    group by users.user_id
)

select * from query1
where first_purchase >= date('2017-01-01') and counttxn > 3

The above query should take both your conditions into account.

Upvotes: 0

Related Questions