How to add a few restrictios to a query?

Question

I have difficulty with syntax...

This is my query:

SELECT t.diapason,
       Count(*) AS 'number_of_users'
FROM   (SELECT CASE
                 WHEN amount < 200 THEN '0-200'
                 WHEN amount >= 200 THEN '200 +'
               end AS diapason
        FROM   (SELECT Sum(amount) AS amount
                FROM   payments) p) t
GROUP  BY t.diapason
ORDER  BY number_of_users DESC;  

But now I need to select only users which had activity.login_time between '2018-01-01' and'2018-01-12'.

So, I think I should use INNER JOIN and set period of time. Bu how?

My tables:

activity
user_id    login_time
1          01.01.2018
2          01.01.2018
3          03.01.2018
4          30.02.2018

payments
user_id    amount   payment_time
1          50       10.12.2017
1          200      09.12.2017
2          40        08.08.2017

what should I change in my query to add activity.login_time?

Output for period 01.01.2018-12.01.2018

diapason     number_of_users
0-200          2
200+           1

Answer 1

You add WHERE clause to filter.

SELECT t.diapason,
       COUNT(*) AS 'number_of_users'
FROM (
    SELECT 
        CASE
            WHEN amount < 200 THEN '0-200'
            WHEN amount >= 200 THEN '200 +'
        END AS diapason
    FROM   (
        SELECT payments.user_id, SUM(amount) AS amount
        FROM   payments
        INNER JOIN activity ON payments.user_id = activity.user_idAND activity.login_time = payments.payment_time
        WHERE activity.login_time BETWEEN '2018-01-10' AND '2018-01-12'
        GROUP  BY payments.user_id
    ) p
) t
GROUP  BY t.diapason
ORDER  BY number_of_users DESC;

Answer 2

I understand your question as this. You had 3 users (user_id=1,2,3) login in the period 01.01.2018-12.01.2018. Of those users, user_id 1 made 2 payments totalling 250, user_id 2 made 1 payment of 40, and user_id 3 made 0 payments so their total is 0. Hence there are 2 values in the range 0-200, and 1 in the range 200 +. If that is the correct understanding, this query will give you the desired results:

SELECT CASE  
         WHEN amount < 200 THEN '0-200'
         WHEN amount >= 200 THEN '200 +'
       END AS diapason,
       COUNT(*) AS number_of_users
FROM (SELECT a.user_id, COALESCE(SUM(p.amount), 0) AS amount
      FROM activity a
      LEFT JOIN payments p ON p.user_id = a.user_id
      WHERE a.login_time BETWEEN '01.01.2018' AND '12.01.2018'
      GROUP BY a.user_id) p
GROUP BY diapason;

Output:

diapason    number_of_users
0-200       2
200 +       1

SQLFiddle demo

Update

To add another row with the total number_of_users, just add WITH ROLLUP to the GROUP BY clause:

SELECT CASE  
         WHEN amount < 200 THEN '0-200'
         WHEN amount >= 200 THEN '200 +'
       END AS diapason,
       COUNT(*) AS number_of_users
FROM (SELECT a.user_id, COALESCE(SUM(p.amount), 0) AS amount
      FROM activity a
      LEFT JOIN payments p ON p.user_id = a.user_id
      WHERE a.login_time BETWEEN '01.01.2018' AND '12.01.2018'
      GROUP BY a.user_id) p
GROUP BY diapason WITH ROLLUP

Output:

diapason    number_of_users
0-200       2
200 +       1
(null)      3

In your application framework you can use the fact that the diapason value is NULL to output something like Total instead.

Updated SQLFiddle

You can also do the same in MySQL (see this SQLFiddle) by wrapping this query up as a subquery and using a COALESCE on the diapason column. In that case the output would be:

diapason    number_of_users
0-200       2
200 +       1
Total       3