Hey there!
I am working with a query with multiple CTEs and wanted to add a Field Filter. However, every time I select a value in the dropdown menu I get this error (if I do not select any value, no errors):
ERROR: invalid reference to FROM-clause entry for table "mdl_customers" Hint: There is an entry for table "mdl_customers", but it cannot be referenced from this part of the query.
I already removed all aliases from the query to make sure there are no issues with them. Any thoughts?
Here is the query:
WITH
emp_in_ind AS (
SELECT
customers.country AS customer_country_name,
customers.customer_name,
contract.job_title,
CASE WHEN contract.contract_end_date <= contract.contract_start_date THEN 1 ELSE 0 END AS terminated_before_start,
CASE WHEN contract.contract_end_date <= current_date THEN 1 ELSE 0 END AS terminated_by_now,
CASE WHEN contract.expiration_date <= current_date THEN 1 ELSE 0 END AS contract_expired_by_now,
CASE WHEN contract.contract_start_date <= current_date THEN 1 ELSE 0 END AS already_started,
contract.salary_source_amount AS base_salary_usd
FROM contract
JOIN customers
ON customers.pk_company_id = contract.company_id
JOIN users
ON contract.user_id = users.pk_user_id
WHERE
-- User filters
users.is_internal = 'false'
AND users.employment_status IN ('active', 'review')
AND users.user_status = 'active'
-- Contract filters
AND contract.contract_status != 'terminated'
AND contract.is_test_contract = 0
AND contract.is_contract_ended = 0
AND contract.is_latest_valid_contract = 1
AND contract.is_contract_ended = 0
AND contract.contract_start_date <= current_date
-- Company filter
AND customers.country IN ('United States', 'United Kingdom', 'Germany')
),
agg_country_job AS (
SELECT
customer_country_name,
job_title,
count(*) AS num_users,
avg(base_salary_usd) AS avg_gross_salary
FROM
emp_in_ind
WHERE
terminated_by_now = 0
AND contract_expired_by_now = 0
AND already_started = 1
AND job_title IS NOT NULL
GROUP BY
1,2
),
ordering AS (
SELECT *,
ROW_NUMBER() OVER (PARTITION BY customer_country_name, job_title ORDER BY num_employees DESC) AS jt_rank
FROM agg_country_job
),
ordering2 as (
SELECT *,
ROW_NUMBER() OVER (PARTITION BY customer_country_name ORDER BY num_employees DESC) AS country_rank
FROM ordering
WHERE jt_rank = 1
),
final AS (
SELECT
customer_country_name,
job_title_group,
job_title,
num_employees,
avg_gross_salary,
country_rank
FROM ordering2
WHERE
country_rank < 11
ORDER BY customer_country_name, country_rank ASC
)
SELECT *
FROM final
WHERE {{company_country_name}}