Comparison of Salaries between School Districts in California

If you are a California state employee, have you ever wondered what your counterparts in other counties and cities within California make? Let us look at the data provided by transparentcalifornia.com.

Transparentcalifornia  provides salary and pension information for all the state employees. I am going to download this data into an Oracle database first and then to 'R' to analyze and generate some interesting graphs.

Let us first look at the school district data. We have created a table "SCHOOL_DISTRICT" with the following columns as shown below. We will import the school district data into the school_district table and run some queries on that.

SCHOOL_DISTRICT

JobTitle	BasePay	OverTimePay	OtherPay	Benefits	TotalPay	TotalPay_Benefits	School_year	Agency
TEACHER	86817.53	0	0	26158.91	86817.53	112976.44	2014	ABC Unified
TEACHER	89501.99	0	5761	16385.89	95262.99	111648.88	2014	ABC Unified

Let us see which position earns the top salary in each school district.

There are 537 distinct school districts in California,so I will pick a few school districts and run the query

SELECT sal.jobtitle,sal.agency,sal.totalpay_benefits FROM (

                SELECT jobtitle, to_char(totalpay_benefits, 'fm9999999.90')totalpay_benefits, agency,

                RANK() OVER (PARTITION BY agency ORDER BY totalpay_benefits desc)"Rank"

                FROM school_district

                WHERE agency IN('Antioch Unified','Bakersfield City School District',
                'TurlockUnified', 'San Francisco Unified', 'Los Angeles Unified', 'Mountain View 
                Whisman' ))sal       

WHERE sal."Rank" < 2

ORDER BY sal.totalpay_benefits DESC

Comparison of Salaries among different School Districts

 

Let us look at the top ten school employees in California who get paid the highest salary.

SELECT jobtitle, to_char(basepay,'fm9999999.90')basepay, overtimepay, to_char(totalpay_benefits,'fm9999999.90')totalpay_benefits, agency FROM

              (SELECT jobtitle,basepay,overtimepay,totalpay_benefits,agency FROM school_district

                ORDER BY totalpay_benefits DESC)

WHERE rownum < 11

Top Ten Salaries of School Employees in CA

Let us also look at the teachers who are in the top ten in terms of salary in California.

SELECT jobtitle,to_char(basepay,'fm9999999.90')basepay, overtimepay, otherpay, benefits, to_char(totalpay_benefits,'fm9999999.90')totalpay_benefits, agency FROM
    (SELECT jobtitle, basepay, overtimepay, totalpay_benefits, agency, otherpay, benefits FROM  school_district
        WHERE jobtitle like 'Teacher%'
            ORDER BY totalpay_benefits DESC)
WHERE rownum < 11

Top Ten Salaries of Teachers in CA

Wow, teachers in Orange County seem to have excellent benefits. Their basepay is also very good. If you are looking for a teaching position, you know which school district you want to be in.

I think by now, you must have gotten an idea of how to find the top ten values in a given data set. You can download this data from transparentcalifornia.com and try these queries and much more on your own.

We will load this data in 'R' and generate some cool graphs in my next post.