Как подсчитать общее количество ожидающих билетов за каждый день на этой неделе в oracle-sql?

Question

Я хочу подсчитать общее количество незавершенных билетов на каждый день этой недели. Я мог получить его только один день за раз. У меня сейчас такой запрос:

    SELECT (n.TOTAL - v.TODAY) + d.GISTER AS GISTER
FROM 
(
    -- Counts yesterday
    SELECT

        COUNT(ID) AS Gister

    FROM FRESHDESK_API
    -- 4 = resolved 5 = closed 
    -- Both count as closed
    WHERE STATUS IN(4, 5)
    AND TRUNC(UPDATED_AT) = TRUNC(SYSDATE - 1)
) d
CROSS JOIN
(
    -- Total pending
    SELECT

        COUNT(ID) AS TOTAL

    FROM FRESHDESK_API
    -- 3 is pending
    WHERE STATUS IN(3) 
) n
CROSS JOIN
(
    -- Pending tickets today
    SELECT

        COUNT(ID) AS TODAY

    FROM FRESHDESK_API
    -- 3 is pending
    WHERE STATUS IN(3) 
    AND TRUNC(UPDATED_AT) = TRUNC(SYSDATE) 
) v

Я хочу получить такой результат:

+----------------------------------+---------+----------+  
|               day                |  pending_tickets   |            
+----------------------------------+---------+----------+  
| Monday                           | 20                 |  
| Tuesday                          | 22                 |  
| Wednesday                        | 25                 |  
| Thursday                         | 24                 |  
| Friday                           | 19                 |  
+----------------------------------+---------+----------+  

Таблица выглядит примерно так (неиспользованные данные не используются):

+----------------------------------+---------+----------+---------+-----------+----------+----------+
|               id                 |     created_at     |     updated_at      |       status        |
+----------------------------------+---------+----------+----------+----------+----------+----------+
|                                  |                    |                     |                     |
|                                  |                    |                     |                     |
|                                  |                    |                     |                     |
|                                  |                    |                     |                     |
|                                  |                    |                     |                     |
+----------------------------------+---------+----------+---------+-----------+---------+-----------+

Answer 1

Вы можете использовать left join и group by следующим образом:

Select to_char(tday.updated_at, 'day') as updated_at, 
       count(tday.id) - count(yday.id) as pending_tickets
  From FRESHDESK_API tday
  Left join FRESHDESK_API yday
       On trunc(tday.UPDATED_AT) = trunc(yday.UPDATED_AT - 1)
       And trunc(yday.UPDATED_AT + 1, 'iw') = trunc(sysdate, 'iw')
       And yday.status in (4,5)
 Where trunc(tday.UPDATED_AT, 'iw') = trunc(sysdate, 'iw')
   And tday.status = 3
Group by to_char(tday.updated_at, 'day'), trunc(tday.updated_at)
Order by trunc(tday.updated_at);