#jobs #jobs #jobs | کاوش روندها در آگهی‌های شغل داده

WITH raw_messages
     AS (SELECT CASE
                  WHEN message_url LIKE '%thread_ts=%' THEN
                  Substring(message_url, '.*thread_ts=(.*)')
                  ELSE Substring(message_url,
                       'https://getdbt.slack.com/archives/C7A7BARGT/(.*)'
                       )
                END AS thread_id,
                message_url,
                created_at,
                text,
                username
         FROM   random_datasets.dbt_jobs_scrape_20220802_messages),
     raw_messages_with_order
     AS (SELECT m.*,
                Row_number()
                  OVER(
                    partition BY thread_id
                    ORDER BY created_at ASC) AS post_order
         FROM   raw_messages m),
     combined_messages
     AS (SELECT m1.thread_id,
                m1.message_url,
                m1.created_at,
                m1.username,
                String_agg (m2.text, ' ') AS combined_text
         FROM   raw_messages_with_order m1
                LEFT JOIN raw_messages_with_order m2
                       ON m1.thread_id = m2.thread_id
                          AND m1.username = m2.username
         -- take message from original poster only
         WHERE  m1.post_order = 1
         GROUP  BY m1.thread_id,
                   m1.message_url,
                   m1.created_at,
                   m1.username)
SELECT *,
       -- location
       CASE
         WHEN Lower(Replace(combined_text, 'Is this role remote?', '')) LIKE
              '%remote%'
       THEN true
         WHEN Lower(combined_text) LIKE '%is this role remote? yes%' THEN true
         ELSE false
       END AS is_remote,
       -- BI stack
       CASE
         WHEN Lower(combined_text) LIKE '%metabase%' THEN true
         ELSE false
       END AS stack_includes_metabase,
       CASE
         WHEN Lower(combined_text) LIKE '%looker%' THEN true
         ELSE false
       END AS stack_includes_looker,
       CASE
         WHEN Lower(combined_text) LIKE '%tableau%' THEN true
         ELSE false
       END AS stack_includes_tableau,
       CASE
         WHEN Lower(combined_text) LIKE '%power bi%' THEN true
         ELSE false
       END AS stack_includes_powerbi,
       CASE
         WHEN Lower(combined_text) LIKE '%hex%' THEN true
         ELSE false
       END AS stack_includes_hex,
       CASE
         WHEN Lower(combined_text) LIKE '%qlik%' THEN true
         ELSE false
       END AS stack_includes_qlik,
       -- role
       CASE
         WHEN Lower(combined_text) LIKE '%analyst%' THEN true
         ELSE false
       END AS role_analyst,
       CASE
         WHEN Lower(combined_text) LIKE '%analytics engineer%' THEN true
         ELSE false
       END AS role_analytics_engineer,
       CASE
         WHEN Lower(combined_text) LIKE '%data scien%' THEN true
         ELSE false
       END AS role_data_scientist,
       CASE
         WHEN Lower(combined_text) LIKE '%data engineer%' THEN true
         ELSE false
       END AS role_data_engineer
FROM   combined_messages
WHERE  combined_text IS NOT NULL