Combine rank and amount in sql

Question

DB - Oracle. All restrictions omitted.

create table customer (cid number(10), cname varchar(50));

create table exercise (eid number(10), ecode varchar(2), score number(2));

-- mapping table
create table customer_exercise (cid number(10), eid number(10), cnt number(10))  

Customer table

cid    cname
100    e1
200    e2
300    e3
400    e4

Exercise table

eid    ecode   score
1      c1       5
2      c2       10
3      c3       6
4      c4       3

Customer_Exercise

cid  eid count
100   1    2
200   2    5
100   2    3
300   4   10 

SQL to get the total -

 SELECT   c.cid
    ,e.eid
    ,COALESCE(SUM(ce.cnt), 0) AS total_cnt
FROM customer c
     CROSS JOIN exercise e
     LEFT JOIN customer_exercise ce
        ON     ce.cid = c.cid
           AND ce.eid = e.eid
WHERE c.cid IN (100, 200, 300)
      AND e.eid IN (1, 2)
GROUP BY c.cid, e.eid
ORDER by c.cid

Result -

c.cid e.eid total_cnt
100    1         2
100    2         3
200    1         0
200    2         5
300    1         0
300    2         0

SQL to calculate the rank for each client -

select cid , RANK() OVER (ORDER BY sum(total_score) desc) as rank from 
(
SELECT   c.cid as cid
    ,e.eid
    ,COALESCE(SUM(ce.cnt), 0) AS total_cnt
    , COALESCE(SUM(ce.cnt), 0) * e.score as total_score
FROM customer c
     CROSS JOIN exercise e
     LEFT JOIN customer_exercise ce
        ON     ce.cid = c.cid
           AND ce.eid = e.eid
WHERE     c.cid IN (100, 200, 300)
     AND e.eid IN (1, 2)
GROUP BY c.cid, e.eid, e.score 
)
GROUP BY cid
ORDER BY rank

Result -

 c.cid  rank
  200    1
  100    2
  300    3

Is it possible to get a result set with one query instead of the two above? I am looking to combine the result of the above two queries into one. The expected result will be published below.

Expected Result -

c.cid   e.eid    total_cnt  rank
200       1      0           1  
200       2      5           1
100       1      2           2
100       2      3           2
300       1      0           3
300       2      0           3

Answer 1

- , , . , - ( , ). WITH: ( CTE, WITH) .

with
     j ( cid, eid, score, cnt ) as (
       select c.cid, e.eid, e.score, ce.cnt
       from   customer c cross join exercise e
              left outer join customer_exercise ce   on c.cid = ce.cid
                                                    and e.eid = ce.eid
       where  c.cid in (100, 200, 300)
         and  e.eid in (1, 2)
     )
select j.cid, j.eid, nvl(j.cnt, 0) as total_count, r.rnk
from   j left join ( select   cid, 
                              rank() over (order by sum(cnt*score) desc nulls last) as rnk
                     from     j
                     group by cid
                   ) r
                     on j.cid = r.cid
order by rnk, eid
;

Answer 2

SUM (...) OVER (...), , .

SELECT  cid, eid, SUM(cnt) AS total_cnt, DENSE_RANK() OVER (ORDER BY cid, total_score DESC) AS rank
FROM
    (
       select c.cid, e.eid, e.score, ce.cnt, SUM(ce.cnt * ce.score) OVER (PARTITION BY c.cid) AS total_score
       from   customer c cross join exercise e
              left outer join customer_exercise ce   on c.cid = ce.cid
                                                    and e.eid = ce.eid
       where  c.cid in (100, 200, 300)
         and  e.eid in (1, 2) 
    ) data   
GROUP BY cid, eid, total_score
ORDER BY rank, eid

Answer 3

:

select cid, eid, score, cnt,
       dense_rank() over (order by coalece(total_score, 0) desc) as rnk
from (select c.cid, e.eid, e.score, ce.cnt,
             sum(e.score) over (partition by c.cid) as total_score
      from customer c cross join
           exercise e left outer join
           customer_exercise ce
           on c.cid = ce.cid and e.eid = ce.eid
      where  c.cid in (100, 200, 300) and e.eid in (1, 2)
     ) ce;

Answer 4

multiple CTE + SUM + DENSE_RANK() MS SQL

;WITH CTE_CUST AS
(
SELECT   cust.cid
        ,exe.eid
        ,exe.score
        ,SUM(ISNULL(cust_exe.cnt,0)) AS total_cnt
FROM    customer cust
        CROSS JOIN
        exercise exe
        LEFT JOIN 
        customer_exercise cust_exe
            ON cust_exe.cid = cust.cid 
                AND cust_exe.eid = exe.eid 
GROUP BY cust.cid, exe.eid,exe.score
)
, CTE_RANK AS
(
SELECT  cid, DENSE_RANK() OVER (order by (SUM(ISNULL((total_cnt * cte.score),0))) DESC) rank_score
FROM    CTE_CUST cte
GROUP BY cid
)
SELECT  cust.cid, cust.eid, cust.total_cnt as 'count', rnk.rank_score 
FROM    CTE_RANK AS rnk
        JOIN
        CTE_CUST AS cust
            ON rnk.cid = cust.cid
WHERE   cust.cid IN (100, 200, 300)
            AND cust.eid IN (1, 2)  
ORDER BY rank_score