How to count total amount of pending tickets for each day this week in oracle-sql?

Question

I want to count the total amount of pending tickets for each day in this week. I was only able to get it for one day at a time. I have this query right now:

    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

I want to get a result like this:

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

The table is someting like this (left the unused data out):

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

Answer 1

You can use left join and group by as follows:

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);