def get_expenditure_percentile(self, obj):
if obj.is_unclassified or self.employer_salary_count == 0:
return 'N/A'
query = '''
WITH expenditure_by_unit AS (
SELECT
COALESCE(employer_parent_id, employer_id) AS unit_id,
SUM(total_pay) AS total_budget
FROM payroll_employer_highest_salaries
WHERE COALESCE(employer_parent_id, employer_id) IN (
SELECT id FROM payroll_employer WHERE taxonomy_id = {taxonomy}
)
AND reporting_year = {reporting_year}
GROUP BY COALESCE(employer_parent_id, employer_id)
),
exp_percentiles AS (
SELECT
percent_rank() OVER (ORDER BY total_budget ASC) AS percentile,
unit_id
FROM expenditure_by_unit
)
SELECT
percentile
FROM exp_percentiles
WHERE unit_id = {id}
'''.format(taxonomy=obj.taxonomy.id,
reporting_year=self.context['data_year'],
id=obj.id)
with connection.cursor() as cursor:
cursor.execute(query)
result = cursor.fetchone()
return format_percentile(result[0] * 100)
def get_percent_of_total_expenditure(self, obj):
department_salaries = self.employer_salaries.values_list('total_pay',
flat=True)
unit_salaries = self.instance.parent.get_salaries(year=self.context['data_year'])\
.values_list('total_pay', flat=True)
return format_percentile(
sum(department_salaries) / sum(unit_salaries) * 100)
def get_salary_percentile(self, obj):
if obj.is_unclassified or obj.parent.is_unclassified:
return 'N/A'
query = '''
WITH taxonomy_members AS (
SELECT
department.id,
department.universe_id
FROM payroll_employer AS unit
JOIN payroll_employer AS department
ON unit.id = department.parent_id
WHERE unit.taxonomy_id = {taxonomy}
AND department.universe_id = {universe}
),
median_salaries_by_department AS (
SELECT
percentile_cont(0.5) WITHIN GROUP (
ORDER BY total_pay ASC
) AS median_salary,
employer_id AS department_id
FROM payroll_employer_highest_salaries
WHERE employer_id IN (
SELECT id FROM taxonomy_members
)
AND reporting_year = {reporting_year}
GROUP BY employer_id
),
salary_percentiles AS (
SELECT
percent_rank() OVER (ORDER BY median_salary ASC) AS percentile,
department_id
FROM median_salaries_by_department
)
SELECT percentile
FROM salary_percentiles
WHERE department_id = {id}
'''.format(taxonomy=obj.parent.taxonomy.id,
universe=obj.universe.id,
id=obj.id,
reporting_year=self.context['data_year'])
with connection.cursor() as cursor:
cursor.execute(query)
result = cursor.fetchone()
return format_percentile(result[0] * 100)