transcriber_payments.sql

\set start_time '\'2018-10-15 00:00:00.0\''
\set end_time '\'2018-10-22 00:00:00.0\''

with calls as (
		SELECT * FROM calls
		INNER JOIN audio_minute_rate_schemes amrs on amrs.id = calls.audio_minute_rate_scheme_id
        LEFT JOIN (
				SELECT DISTINCT ON(call_id) rating as tqr_rating, created_at, call_id
				FROM transcript_quality_ratings
				ORDER BY call_id, created_at DESC
        ) AS transcript_quality_ratings ON transcript_quality_ratings.call_id = calls.id
        LEFT JOIN (
				SELECT DISTINCT ON(call_id) rating, created_at, call_id
				FROM transcript_quality_ratings earliest_transcript_ratings
				ORDER BY call_id, created_at ASC
        ) AS earliest_transcript_ratings ON earliest_transcript_ratings.call_id = calls.id
		WHERE	1=1
		AND 	transcript_quality_ratings.created_at BETWEEN :start_time AND :end_time
		AND 	earliest_transcript_ratings.created_at >= :start_time
),

transcriber_stats as (
		SELECT
				contractor_users.id,
				first_name || ' ' || last_name AS name,
				email,
				payment_method,
				paypal_account,
				--  upwork_profile,
				SUM(CASE
					WHEN calls.tqr_rating = 1 THEN (call_length_sec / 60.0 * calls.rate_1 / 100.0)
					WHEN calls.tqr_rating = 2 THEN (call_length_sec / 60.0 * calls.rate_2 / 100.0)
					WHEN calls.tqr_rating = 3 THEN (call_length_sec / 60.0 * calls.rate_3 / 100.0)
					WHEN calls.tqr_rating = 4 THEN (call_length_sec / 60.0 * calls.rate_4 / 100.0)
					WHEN calls.tqr_rating = 5 THEN (call_length_sec / 60.0 * calls.rate_5 / 100.0)
					ELSE 0.0
				END) AS base_payment,
				round(SUM(calls.call_length_sec) / 60.0, 1) AS total_minutes,
				round(SUM(CASE WHEN calls.tqr_rating >= 3 OR
									calls.tqr_rating IS NULL
						  THEN call_length_sec
						  ELSE 0 END) / 60.0, 1) AS paid_minutes,
				COUNT(*) AS total_transcripts,
				SUM(CASE WHEN calls.tqr_rating < 3 THEN 1 ELSE 0 END) AS unacceptable_transcripts,
				round(AVG(calls.tqr_rating), 1) AS avg_rating
		FROM contractor_users
		INNER JOIN calls on calls.transcriber_id = contractor_users.id
		GROUP BY contractor_users.id
),

transcriber_bonuses as (
		SELECT
				transcriber_stats.*,
				CASE
						WHEN transcriber_stats.paid_minutes >= 600 THEN 22.5
						WHEN transcriber_stats.paid_minutes >= 400 THEN 12.5
						WHEN transcriber_stats.paid_minutes >= 200 THEN 5
						ELSE 0 END as bonus

		FROM transcriber_stats
),

payments as (
		SELECT
				transcriber_bonuses.*,
				base_payment + bonus AS total_payment
		FROM transcriber_bonuses
		ORDER BY payment_method, total_payment, name
)

SELECT sum(total_payment) from payments;