SQL : How to subquery using outer alias with case expression

How can I create a subquery that uses the value of an outer column for a subquery which uses a case expression?

SQL Example:

SELECT
    name,
    average,
    crnt,
    goodFactor
FROM
(
 "Crispy Taco" AS name,
  ( SELECT avg(x)
    FROM (
     SELECT COUNT() x
     FROM tacos
     WHERE orderDate > DATEADD(DAY,  -1, GETDATE()))   <--- THE AVERAGE PRIOR TO TODAY
  ) AS average,
  COUNT(*) AS crnt,
  (
    SELECT
    CASE WHEN crnt < average    <---- PROBLEM HERE : Missing columns 'crnt' and 'average'
     THEN "not good"
     ELSE "pretty good"
    END
  ) AS goodFactor
)
from tacos

The problem:

'crnt' and 'average' are 'missing columns' for the 'goodFactor' subquery

Sample Data : (database entries)

+-------------+---------+---------+
|    Name     | Ordered |  Date   |
+-------------+---------+---------+
| Crispy Taco |       1 | 8/29/24 |
| Crispy Taco |       1 | 8/29/24 |
| Crispy Taco |       1 | 8/29/24 |
| Crispy Taco |       1 | 8/29/24 |
| Crispy Taco |       1 | 8/29/24 |
| Crispy Taco |       1 | 8/30/24 |
| Crispy Taco |       1 | 8/30/24 |
| Crispy Taco |       1 | 8/30/24 |
| Crispy Taco |       1 | 8/30/24 |
| Crispy Taco |       1 | 8/30/24 |
| Crispy Taco |      12 | 8/31/24 | <---- ASSUME QUERY INVOKED THIS DAY PRODUCING THE TABLE BELOW
+-------------+---------+---------+

The expected results:

if 'crnt' value is greater than the computed 'average' then return "pretty good" else return "not good"

Expected Result

+-------------+---------------+---------+--------------------+
|    Name     | Daily Average | Current | GoodFactor         |
+-------------+---------------+---------+--------------------+
| Crispy Taco |       5       |      12 | pretty Good        |
+-------------+---------------+---------+--------------------+

This should work in all standard DBMS-s - today on 31st August 2024, that is, otherwise you will have to replace CURRENT_DATE with 31st August 2024, everywhere, with that sample data:

WITH                                                                                                                                                                           
-- your input , don't use in final query
indata(name,ordered,date) AS (
          SELECT 'Crispy Taco', 1,DATE '08/29/24'
UNION ALL SELECT 'Crispy Taco', 1,DATE '08/29/24'
UNION ALL SELECT 'Crispy Taco', 1,DATE '08/29/24'
UNION ALL SELECT 'Crispy Taco', 1,DATE '08/29/24'
UNION ALL SELECT 'Crispy Taco', 1,DATE '08/29/24'
UNION ALL SELECT 'Crispy Taco', 1,DATE '08/30/24'
UNION ALL SELECT 'Crispy Taco', 1,DATE '08/30/24'
UNION ALL SELECT 'Crispy Taco', 1,DATE '08/30/24'
UNION ALL SELECT 'Crispy Taco', 1,DATE '08/30/24'
UNION ALL SELECT 'Crispy Taco', 1,DATE '08/30/24'
UNION ALL SELECT 'Crispy Taco',12,DATE '08/31/24'
)
-- real query starts here, replace following comma with "WITH"
,
-- need a query grouping by day to get the daily sums
-- before today
daily AS (
  SELECT 
    name
  , date
  , SUM(ordered) AS per_day
  FROM indata
  WHERE date < CURRENT_DATE
  GROUP BY  
    name
  , date
)
,
-- and one summing the current day
current AS (
  SELECT
    name
  , date
  , SUM(ordered) AS current
  FROM indata
  WHERE date=CURRENT_DATE
  GROUP BY
    name
  , date
)
SELECT
  daily.name
, AVG(per_day) AS "Daily Average"
, MAX(current) AS current
, CASE WHEN MAX(current) < AVG(per_day)
    THEN 'not good'
    ELSE 'pretty good'
  END AS good_factor
FROM daily
JOIN current ON daily.name = current.name
GROUP BY  
  daily.name;