SQL - Trending based on usage and month

Question

I'm having a problem to create a trend based on data from database.

I've tried by using this query; but multiple queries is needed. Besides, I would like to know if there any better practice to do this.

SELECT DISTINCT TOP 1
    billing.Accounts,
    billing.LastUsage,
    billing.CurrentUsage,
    billing.Total,
    billing.TIME AS Issued

FROM billing
    WHERE
        billing.TIME >= '2016-01-01'
    AND
        billing.TIME <= '2016-04-03'
    AND [top usage account]

ORDER BY
    billing.Total DESC

Here is my database layout :

ID      Accounts    LastUsage   CurrentUsage    Total       TIME
----    --------    ---------   ------------    ------      -------
1       26 [FK]         150             200         50      2016-02-01 HH:mm:ss
2       26 [FK]         200             500        300      2016-03-23 HH:mm:ss
3       26 [FK]         500             800        300      2016-04-05 HH:mm:ss
4       27 [FK]           0             250        250      2016-04-05 HH:mm:ss
5       27 [FK]         800            1200        400      2016-06-05 HH:mm:ss

What I'm trying to achieve

Firstly, I want to know which is heavy user based on usage this for month and after that it will retrieve all records that available from previous 3 months ago to make it as a trend.

Answer 1

You can try the below query

select TOP 3 
    month(b1.time) monthnum,
    sum(total) monthlyusage
from billing b1 right join
    (
    select 
        top 1 Accounts, month(Time) monthnum from billing 
    where 
        --if using current datetime then use the commented portion below
        eomonth(Time)=eoMONTH(getdate()) 
        --Time >= '2016-01-01' AND TIME <= '2016-04-03'
    order by 
        sum(Total) over(partition by Accounts, month(Time) order by Accounts) desc
    ) b2
    on b1.Accounts=b2.Accounts 
group by month(b1.time)
order by month(b1.time)

SQL fiddle demo

UPDATE

See my sql fiddle results