Skip to content

Instantly share code, notes, and snippets.

@austindoeswork
Created October 23, 2018 22:38
Show Gist options
  • Save austindoeswork/ba89201749e5e3a02c1bc25d21c61b7f to your computer and use it in GitHub Desktop.
Save austindoeswork/ba89201749e5e3a02c1bc25d21c61b7f to your computer and use it in GitHub Desktop.
\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;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment