Lesson 13 of 13 · 24 min
Incentive analysis: bonuses, ratings and pay
By the end of this lesson you can analyse incentive payments against performance ratings, express bonuses as a percentage of base salary as of the right date, find eligible employees who were not paid, and aggregate incentive cost without fan-out.
Masar Holding pays three kinds of incentive, recorded in incentive_payments (one row per payment: employee_id, plan, performance_year, paid_on, amount):
annual_bonus: paid in March for the previous year's performance (the 2024 bonus was paid in March 2025; no 2025 bonus has been paid yet);sales_commission: paid quarterly to sales staff, after the quarter;spot_award: small one-off recognitions.
Performance comes from performance_reviews (one row per employee per review_year, rating 1–5, NULL when the review is pending or the person joined too late). Salary comes from salary_history (the row in force on a date, as in the previous lesson).
The key modelling point: the bonus for performance year Y relates to the review for year Y, and to the salary in force at the end of Y, not to the salary on the payment date (which may already include the next annual increase) and not to the latest salary ever.
This lesson, its 4 graded exercises and every other Members lesson in SQL Quest come with a Formula Quest membership that includes SQL (Single Quest with SQL selected, Quest Trio including SQL, or Quest Unlimited), or with SQL access from your organization.
Free to everyone: all of SQL Foundations and Retrieving and Filtering Data, the first lesson of every track, the playground, the dataset library and the SQL reference. See free lessons