I am working on a view in Athena, which requires being able to do the following, identify the time of each work experience, using the number of months, days and count how many jobs you have had in your work experience and the month field is not being recognized in the code, for that the following was done:
CREATE OR REPLACE VIEW "bi_applied_offers" AS
SELECT DISTINCT
applied_offers.id application_id,
applied_offers.applied_date applied_date,
year(applied_offers.applied_date),
month(applied_offers.applied_date),
... [rest of your SELECT fields here] ...
ARRAY_JOIN(ARRAY_AGG(DISTINCT concat(concat(concat(concat(concat(concat(concat(concat(concat(concat(work_experiences.company_name, ' '), COALESCE(CAST(work_experiences.started_at AS varchar), '-')), ' a '), COALESCE(CAST(work_experiences.finished_at AS varchar), 'En progreso')), ' durante '), COALESCE(CAST(work_experiences.time_period AS varchar), '')), ' meses como '), work_positions_work_experiences.description), ' --- '), job_categories_work_experiences.description)), ', ') work_experiences_description,
COUNT(DISTINCT work_experiences.id) AS num_of_experiences,
SUM(
CASE
WHEN work_experiences.finished_at IS NOT NULL THEN
DATEDIFF(MONTH, work_experiences.started_at, work_experiences.finished_at)
ELSE
DATEDIFF(MONTH, work_experiences.started_at, CURRENT_DATE)
END
) AS total_experience_duration_months
FROM
... [your JOINs and other clauses here] ...
WHERE (applied_offers.created_at > CAST('2019-01-01' AS DATE))
GROUP BY applied_offers.id, applied_offers.applied_date, year(applied_offers.applied_date), month(applied_offers.applied_date), ... [rest of your GROUP BY fields here] ...
I keep having an error:line 66:24: Column 'application_month' cannot be resolved. I have been making the changes
I already made the suggested changes and the same error continues to appear.
The month field does not exist initially, I am looking to extract it from the applied_date field that has 2022-09-27 16:34:58.130