CODEWITHSUNDEEP
sqlpostgresqlnetezza
Naj
Naj

Reputation: 47

Top 10 based on last month showing 6 previous months

I want to show a graph with income from different parties over the last 6 months, but based on the top income of 10 people only based on the last month.

So this can change each month as the top 10 people can change when they deposit more money, so the graph will show these 10 people's deposits of the last 6 months, based on the last month deposit only.

I already used a LAG function and a RANK() OVER PARTITION function.

Upvotes: 1

Views: 93

Answers (1)

sagi
sagi

Reputation: 40481

I don't understand why you'll need rank or lag functions.

You can simply use an IN statement:

SELECT * FROM YourTable t
WHERE t.depositDate between StartRangeDate and EndRangeDate
AND t.ID in(select ID from(SELECT s.id,sum(s.depositAmount) as total
                           from YourTable s
                           where s.date between ThisMonthStart and ThisMonthEnd
                           group by s.id)
            order by total
            limit 10)

You can play with the first select to select what ever you want/add a group by and sum them or I don't know.

Upvotes: 1

Related Questions