Avoiding Ghost Jobs

How I avoid applying to ghost jobs and job spam in general.

What Are Ghost Job Posts?

according to this article 50% of job listings do not result in the candidate being hired. This number is up from 20% in 2018. This is a huge problem if you’re seeking work the traditional way. These types of jobs are often referred to as “ghost jobs”.

How you can avoid ghost jobs

If you’re not a developer or a tech enthuiast, you might not be able to take this approach, but if you are a little familiar with programming concepts you may be able to use this technique.

A little SQL

I’m using PostgreSQL for this example, but the code should work with slight adjustments on other SQL based databases.

1. Create a table to maintain the a unique list of companies and their statistics related our our job search.

CREATE TABLE public.unique_companies (
    id serial4 NOT NULL,
    companyname text NOT NULL,
    job_count int4 NOT NULL,
    last_updated timestamp DEFAULT CURRENT_TIMESTAMP NULL,
    applications_count int4 DEFAULT 0 NULL,
    CONSTRAINT unique_companies_companyname_key UNIQUE (companyname),
    CONSTRAINT unique_companies_pkey PRIMARY KEY (id)
);

This tables stores a unique id for each company, the company name, the number of jobs posted for the company, the last date the company was updated, and the number of applications sent to the company.

2. Create a function to update the unique_companies table whenever we insert or update a job listing. The update_unique_companies() function is the core logic for this operation. We’ll create this function using PL/pgSQL (Procedural Language/PostgreSQL Structured Query Language).

Note on PL/pgSQL PL/pgSQL is a procedural programming language that let’s up perform complex operations on our database that we normally can’t do with vanilla SQL.

Here’s what our update_unique_companies() function looks like:

CREATE OR REPLACE FUNCTION public.update_unique_companies()
 RETURNS trigger
 LANGUAGE plpgsql
AS $function$
BEGIN
    INSERT INTO public.unique_companies (companyname, job_count, website, logo, applications_count)
    SELECT 
        NEW.companyname,
        COUNT(*) as job_count,
        SUM(CASE WHEN hasapplied > 0 THEN 1 ELSE 0 END) as applications_count
    FROM public.jobs
    WHERE companyname = NEW.companyname
    GROUP BY companyname
    ON CONFLICT (companyname) DO UPDATE SET
        job_count = EXCLUDED.job_count,
        applications_count = EXCLUDED.applications_count,
        last_updated = CURRENT_TIMESTAMP;
    
    RETURN NEW;
END;
$function$

2. Query our jobs table and only get jobs that we haven’t applied to in the last 30 days.

SELECT j.id, j.posttitle, j.companyname, j.locality, j.hasapplied, j.link
FROM jobs j 
JOIN unique_companies uc 
    ON j.companyname = uc.companyname
WHERE 
    (uc.applications_count < 1 OR uc.last_updated <= CURRENT_DATE - INTERVAL '30 days')
    AND j.hasapplied = 0
    AND (j.locality ilike '%remote%' or j.locality ilike '%United States%');

Our results might look like this:

Note on Job Storage The jobs we save are stored in a separate table from the unique_companies table. This allows us to maintain a detailed record of each job listing while keeping track of company-level statistics in the unique_companies table.

Conclusion

Now when we query our jobs table we only see jobs that we have haven’t applied to before, or in the last 30 days. So, even though ghost jobs are still out there, we only have to waste our time on one of them per company.

If you have any questions or experiences with ghost job posts, feel free to share them in the comments below or email me at blakelinkd@gmail.com.