I am working on a SQL query in Metabase where I need to handle user IDs dynamically through a variable. This involves two tables: reporte_disponibilidad_2 and usuario_cliente_final, with aliases r and ucf respectively. The query calculates the sum of inactive and active hours based on the client ID and user status from these tables, and then computes the percentage of these hours relative to the total hours.
Here’s the core structure of the SQL query I am using:
WITH estados AS (
SELECT
'Inactivos' AS estado,
SUM(r.inactivo) AS horas
FROM
public.reporte_disponibilidad_2 AS r
JOIN
public.usuario_cliente_final AS ucf ON r.idcliente = ucf.iddestino
WHERE
ucf.idusuario = COALESCE({{user_id}}, 29) -- 'user_id' is handled as a dynamic variable, with a default value of 29 if not provided
AND ucf.activo = TRUE
[[AND {{fecha}}]] -- Optional dynamic date filter
[[AND ({{cliente}} IS NULL OR r.idcliente = ANY({{cliente}}))]] -- Optional client filter
UNION ALL
SELECT
'Activos' AS estado,
SUM(r.activo) AS horas
FROM
public.reporte_disponibilidad_2 AS r
JOIN
public.usuario_cliente_final AS ucf ON r.idcliente = ucf.iddestino
WHERE
ucf.idusuario = COALESCE({{user_id}}, 29)
AND ucf.activo = TRUE
[[AND {{fecha}}]]
[[AND ({{cliente}} IS NULL OR r.idcliente = ANY({{cliente}}))]]
),
total_horas AS (
SELECT SUM(horas) AS total_horas FROM estados
)
SELECT
estado,
horas AS "Horas",
ROUND((horas / total_horas.total_horas) * 100, 2) AS "Porcentaje"
FROM
estados
CROSS JOIN
total_horas;
Problem: I keep encountering an error: "ERROR: invalid reference to FROM-clause entry for table 'usuario_cliente_final'. Hint: Perhaps you meant to reference the table alias 'ucf'." The error points to a specific position in the query, suggesting an issue with how I'm referencing the table or its alias. I've double-checked the aliases and they seem to be used correctly throughout the query.
Could you please help me identify what might be causing this issue, or suggest any changes that might resolve the error? Additionally, any insights on effectively handling dynamic variables like user IDs in Metabase would be greatly appreciated.