mysql - How it is better to join a few tables (including subqueries) in one query?

Question

How it is better to join a few tables (including suqueries) in one query?

For example, I've got 2 tables: activity (user_id, login_time) and payments (user_id, payment_time, amount). Both tables conclude duplicate id's.

The task is to select unique user_id, which ever payed, with login_time from 05.04.12 until 10.04.12. Then range the by group accotding of total amount.

My query is:

SELECT t.diapason, COUNT(*) AS 'number_of_users'
FROM (SELECT CASE  
    when amount<100 then '0-100' 
    when amount>=100 then '100 +' END AS diapason
    FROM 
        (SELECT SUM(amount) AS amount 
        FROM payments GROUP BY payments.user_id) p) t
  GROUP BY t.diapason
  ORDER BY number_of_users desc;

I dont know where should I use 'inner join a....between '2012-04-05' and'2012-04-10''

Please help, thanks!

DATA SAMPLE

activity
user_id    login_time
1          05.04.2012
2          05.04.2012
3          06.04.2012
4          30.05.2012

payments
user_id    amount   payment_time
1          50       10.12.2011
1          20        09.12.2011
2          400        08.08.2011

Output for period 05.04.2012-10.04.2012

diapason     number_of_users
0-100          2
100+           1

Answer 1

I believe this gets you what you're looking for (one of many ways):

SELECT t.diapason, COUNT(*) AS 'number_of_users'
FROM (SELECT CASE  
    when amount<100 then '0-100' 
    when amount>=100 then '100 +' END AS diapason
    FROM 
        (SELECT SUM(amount) AS amount 
        FROM payments 
        INNER JOIN (SELECT DISTINCT user_id FROM activity where login_time between '2012-04-05' and '2012-04-12') AS a ON payments.user_id = a.user_id
        GROUP BY payments.user_id) AS P
      ) t
GROUP BY t.diapason
  ORDER BY number_of_users desc
;