Best clients

best_clients.sql

SELECT u.firm_name AS 'Компания',
  ROUND(SUM(CASE WHEN p.t_time BETWEEN DATE(DATE_ADD(NOW(), INTERVAL -30 day)) AND DATE(NOW()) THEN p.amount ELSE 0 END),2) AS 'За весь месяц' ,
  ROUND(SUM(CASE WHEN p.t_time BETWEEN DATE(DATE_ADD(NOW(), INTERVAL -7 day)) AND DATE(NOW()) THEN p.amount ELSE NULL END),2) AS 'За 1 неделю',
  ROUND(SUM(CASE WHEN p.t_time BETWEEN DATE(DATE_ADD(NOW(), INTERVAL -14 day)) AND DATE(DATE_ADD(NOW(), INTERVAL -7 day)) THEN p.amount ELSE NULL END),2) AS 'За 2 неделю',
  ROUND(SUM(CASE WHEN p.t_time BETWEEN DATE(DATE_ADD(NOW(), INTERVAL -21 day)) AND DATE(DATE_ADD(NOW(), INTERVAL -14 day)) THEN p.amount ELSE NULL END),2) AS 'За 3 неделю',
  ROUND(SUM(CASE WHEN p.t_time BETWEEN DATE(DATE_ADD(NOW(), INTERVAL -28 day)) AND DATE(DATE_ADD(NOW(), INTERVAL -21 day)) THEN p.amount ELSE NULL END),2) AS 'За 4 неделю'
  FROM payments p
  JOIN user u
  ON p.user_id=u.id
  WHERE p.status='sign'
GROUP BY u.firm_name
ORDER BY 2 DESC
LIMIT 10;