Average Job Posting Duration (days)

SELECT
  "source"."average_posting_days" AS "average_posting_days"
FROM
  (
    SELECT
      -- Calculate the average of the difference between the closing and creation dates
      AVG(date_closing :: date - date_created :: date) AS average_posting_days
    FROM
      Jobs
   
WHERE
      -- Only include jobs that are marked as published
      status = 'published' -- Ensure we only calculate for jobs that have a valid closing and creation date
     
   AND date_closing IS NOT NULL
      AND date_created IS NOT NULL -- Avoid errors from bad data where closing is before creation
      AND date_closing > date_created
  ) AS "source"
LIMIT
  1048575