Salaries Categories Statistics ideas - RobertCzaja/it-offers-api GitHub Wiki

30.09.2024

Salary looks like that 17000 - 21000 PLN - I could think about some stats that checks what is the gap between from and to.

---

select
    ori.provider,
    o.technology,
    o.title,
    concat(s.amount_from,' - ',s.amount_to,' ',s.currency, ' (',s.employment_type ,')'),
    string_agg(c.name, ', ') categories,
    cmp.name as companyName,
    cmp.city
from offer o
         left join offer_salary os on o.id = os.offer_id
         left join salary s on s.id = os.salary_id
         left join offer_categories oc on o.id = oc.offer_id
         left join category c on c.id = oc.categories_id
         left join company cmp on o.company_id = cmp.id
         left join origin ori on ori.id = o.origin_id
where s.currency = 'PLN' and o.technology = 'php'
group by o.title, s.amount_to, s.amount_from, s.currency, o.technology,s.employment_type,cmp.name,cmp.city, ori.provider
order by s.amount_to desc

Volt's offers

select
    ori.provider,
    o.created_at,
    o.technology,
    o.title,
    concat(s.amount_from,' - ',s.amount_to,' ',s.currency, ' (',s.employment_type ,')'),
    string_agg(c.name, ', ') categories,
    c.name as companyName,
    c.city
from company c
join offer o on o.company_id = c.id
left join offer_salary os on o.id = os.offer_id
left join salary s on s.id = os.salary_id
left join origin ori on ori.id = o.origin_id
where c.name ilike '%volt%'
group by o.created_at,o.title, s.amount_to, s.amount_from, s.currency, o.technology,s.employment_type,c.name,c.city, ori.provider
order by o.created_at asc