Oracle grouped row counts to columns
Possible with a CTE, but PIVOT is the easier implementation.

If for total_fio you still wanted distinct, this would work:

SELECT * FROM
 (SELECT pc_id, 
         user_id, 
         to_char(payment_datetime,'DD.MM.YYYY') AS "date", 
         To_char(payment_datetime, 'HH24') AS "hour",
         fio,
         count(DISTINCT fio) OVER ( PARTITION BY  to_char(payment_datetime,'DD.MM.YYYY') ) AS total_fio
  FROM   foobar 
  WHERE  To_char(payment_datetime, 'HH24') BETWEEN '07' AND '22' )
PIVOT
 (Count(DISTINCT fio)
   FOR "hour" IN ('07', '08', '09', '10', '11', '12', 
                  '13', '14', '15', '16', '17', '18', 
                  '19', '20', '21', '22'));


Or if you wanted a sum of all hour columns, I'm not sure of an elegant way to do it without getting into PL/SQL:

SELECT x.*, "07" + "08" + "09" + "10" + "11" + "12" 
             + "13" + "14" + "15" + "16" + "17" + "18" 
             + "19" + "20" + "21" + "22" AS total_fio
FROM
(SELECT pc_id, 
        user_id, 
        to_char(payment_datetime,'DD.MM.YYYY') AS date_day, 
        To_char(payment_datetime, 'HH24') AS date_hour,
        fio
 FROM   foobar 
 WHERE  To_char(payment_datetime, 'HH24') BETWEEN '07' AND '22')
PIVOT
(Count(DISTINCT fio)
  FOR date_hour IN ('07' AS "07", '08' AS "08", '09' AS "09", '10' AS "10", '11' AS "11", 
                    '12' AS "12", '13' AS "13", '14' AS "14", '15' AS "15", '16' AS "16", 
                    '17' AS "17", '18' AS "18", '19' AS "19", '20' AS "20", '21' AS "21", 
                    '22' AS "22")) x;


I'm new on here so don't have enough rep to comment on your implementation, but it's a solid go at it for sure. I don't see anything wrong where you'd get inconsistent results. As for optimization, `PIVOT' does the grouping for you. So by using the CTE with the additional group by to start out, it is almost doubling the work the query has to do. My second query above is essentially an optimized version of yours (they are very similar).