CODEWITHSUNDEEP
sqlpostgresqlfilteringranking
Bagbyte
Bagbyte

Reputation: 863

Postgres get sales for top account with ranking

I have the following tables:

Account (id, name)
Solution (id, name)
Sales (solution_id, account_id, month, year, amount)

I need to calculate the monthly sales of each account in a specific period:

SELECT
  to_char(make_date(sales.year, sales.month, 1), 'YYYY-MM') AS period,
  acc.id AS account_id,
  acc.name AS account_name,
  COALESCE(SUM(sales.net_sales), 0) AS amount
FROM
  (SELECT * 
   FROM sales
   WHERE make_date(year, month, 1) >= FROM_DATE
     AND make_date(year, month, 1) <= TO_DATE) sales
  INNER JOIN account acc.id = sales.account_id
GROUP BY sales.year, sales.month
ORDER BY sales.year, sales.month ASC

I can now calculate the total sales, in the period in the range:

SELECT
  to_char(make_date(sales.year, sales.month, 1), 'YYYY-MM') AS period,
  acc.id AS account_id,
  acc.name AS account_name,
  COALESCE(SUM(sales.net_sales), 0) AS amount
FROM
  (SELECT *, COALESCE(SUM(net_sales) OVER (PARTITION BY client_id), 0) AS total
   FROM sales
   WHERE make_date(year, month, 1) >= FROM_DATE
     AND make_date(year, month, 1) <= TO_DATE) sales
  INNER JOIN account acc.id = sales.account_id
GROUP BY sales.year, sales.month
ORDER BY sales.year, sales.month ASC

Is there a way to rank the total sales in order to get only the n top account in the selected period?

Upvotes: 0

Views: 285

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270713

Your queries are a bit of a mess. The first is not syntactically correct. I think you can simplify and the intention is:

SELECT to_char(make_date(s.year, s.month, 1), 'YYYY-MM') AS period,
       a.id AS account_id, a.name AS account_name,
       COALESCE(SUM(s.net_sales), 0) AS amount,
       SUM(SUM(s.net_sales)) OVER (PARTITION BY a.id) as total
FROM sales s INNER JOIN
     account a
     ON a.id = s.account_id
WHERE make_date(s.year, s.month, 1) >= FROM_DATE AND
      make_date(s.year, s.month, 1) <= TO_DATE
GROUP BY s.year, s.month, a.id, a.name
ORDER BY s.year, s.month ASC;

If you want to rank by total sales (or monthly sales), then you can use dense_rank():

SELECT ym.*
FROM (SELECT to_char(make_date(s.year, s.month, 1), 'YYYY-MM') AS period,
             a.id AS account_id, a.name AS account_name,
             COALESCE(SUM(s.net_sales), 0) AS amount,
             total,
             DENSE_RANK() OVER (ORDER BY total DESC) as seqnum
      FROM (SELECT s.*, SUM(s.net_sales) OVER (PARTITION BY client_id) as total
            FROM sales s
           ) s INNER JOIN
           account a
           ON a.id = s.account_id
      WHERE make_date(s.year, s.month, 1) >= FROM_DATE AND
            make_date(s.year, s.month, 1) <= TO_DATE
      GROUP BY s.year, s.month
     ) ym
WHERE seqnum <= 3
ORDER BY s.year, s.month ASC;

Upvotes: 2

Related Questions