- *******************************************************************************
- *******************************2021-05-01*******************
- ************************2021-01-01 ************************10000.
- ********************************************************************************************** ****************************************************************************************************
- ***************************************************
Table: amzn_employees
col_name | col_type
-------------+------------------
employee_id | bigint
first_name | text
last_name | text
joined_date | date
salary | float
Solution postgres
SELECT
first_name,
last_name,
CASE WHEN joined_date >= '2021-05-01' THEN 0
WHEN joined_date < '2021-01-01' THEN 10000
ELSE ('2021-05-01' - joined_date) * 1.0 / 120 * 10000 END AS bonus
FROM amzn_employees;
Explanation
This query selects the first name, last name, and calculated bonus amount for all employees in the "amzn_employees" table. The bonus amount is calculated based on the employee's "joined_date" field. If the employee joined on or after May 1st, 2021, the bonus amount is 0. If the employee joined before January 1st, 2021, the bonus amount is 10,000. For employees who joined between January 1st and May 1st, 2021, the bonus amount is calculated as a proportion of time worked during that period, divided by the total time in that period, and multiplied by 10,000. The result is returned as a new column labeled "bonus".
Copied
Expected results
Your results