Some useful SQL stats (data-science-esque)

churn.sql

SELECT paymentcount, CONCAT(ROUND(((cnt - lag(cnt) over (ORDER BY paymentcount)) / lag(cnt) over (ORDER BY paymentcount)) * 100), '%')
    as perc_change, cnt as cnt
from (SELECT COUNT(paymentcount) as cnt,paymentcount FROM
             (select COUNT(*) as paymentcount From Payments JOIN Users on Users.id = Payments.client
             where Users.id != 1 GROUP BY Users.id)
                 as a group by paymentcount) as b

monthly_income.sql

SELECT sum,
       CONCAT(year, ' ', month) date,
       CONCAT(ROUND(((sum - lag(sum) over (ORDER BY year*100+month)) / lag(sum) over (ORDER BY year*100+month)) * 100), '%') perc_change
FROM (select sum(amount) as sum,
       DATE_FORMAT(rawdate, '%m') as month, DATE_FORMAT(rawdate, '%Y') as year
FROM (SELECT STR_TO_DATE(`date`, '%d-%m-%Y %k:%i') as rawdate, amount FROM Payments where (state = 'approved' or state = 'paid') and client != 1
                                                   ) orig GROUP BY year, month order by rawdate) o order by year, month

percentile_ranks.sql

SELECT sum(amount) as amount, 
       ROUND(percent_rank() over (order by amount), 4) perc_rank,
       Users.id as id,
       Users.username as username
from Payments 
    JOIN Users on Users.id = Payments.client 
GROUP by id 
ORDER BY amount DESC;