let me explain what im trying to achieve (im using Bigquery):

I have two tables, one called requests and another one called incidents, in the requests table there are records by application that save every request with its status code 200, 500, 404, etc. In the incidents table there are also records of incidents by each application.

I need to make a report that has each application name, and then count the number of 200 request, count the number of any request that was not 200 and finally the number of incidents of each application.

I managed to make a query that works but there is a “rare” scenario where there can be incidents for an application, but no records at the request table (no 200 requests, no 500 requests, nothing), so in that case my query does not return the incident count since it could not find records at the requests table for that same application. I need help to still show that particular application name, with the count of requests on 0 and the incident count correctly, but have not been able to achieve this, here is my query:

SELECT
  directoryName AS directoryProviderId, -- this is the app name
  COUNTIF(operationStatus = 200) AS successfulOps,
  COUNTIF(operationStatus <> 200) AS failedOps,
  (
  SELECT
    COUNT(*) AS incidentCount
  FROM
    `incidents` i
  WHERE
    r.directoryName=i.directoryProviderId
    -- Only get incidents from yesterday (starts from yesterday at 00:00 until yesterday at 23:59 Lima time)
    AND DATETIME(TIMESTAMP_MILLIS(i.timestamp), "America/Lima") >= DATE_SUB(CURRENT_DATE('America/Lima'), INTERVAL 1 DAY)
    AND DATETIME(TIMESTAMP_MILLIS(i.timestamp), "America/Lima") < CURRENT_DATE('America/Lima')) AS incidents
FROM
  `requests` AS r
WHERE
  -- Only get requests from yesterday (starts from yesterday at 00:00 until yesterday at 23:59 Lima time)
  DATETIME(TIMESTAMP_MILLIS(r.timestamp), "America/Lima") >= DATE_SUB(CURRENT_DATE('America/Lima'), INTERVAL 1 DAY)
  AND DATETIME(TIMESTAMP_MILLIS(r.timestamp), "America/Lima") < CURRENT_DATE('America/Lima')
GROUP BY
  directoryName

The requests table has the following columns:
requestId (String), operationStatus (Integer), timestamp (Integer), directoryName (String)

The incidents table has the following columns:
incidentId (String), directoryProviderId (String), timestamp (Integer)