I have three tables. The first one is called 'Orders' where the order dates are stored along with the employee that handled the orders.
| order_date | employee_id |
|---|---|
| 01/08/2015 | 101 |
| 01/08/2015 | 101 |
| 01/08/2017 | 102 |
| 01/08/2017 | 119 |
The second table is called 'Employee_Hours'. This is where the total duration of work time per employee is stored per day.
| employee_id | total_work_in_seconds | shift_date |
|---|---|---|
| 101 | 1900 | 01/08/2015 |
| 118 | 1850 | 01/08/2015 |
| 119 | 2250 | 01/08/2017 |
The third table is 'Employees'. This is where the employee names and id are stored.
| name | employee_id |
|---|---|
| John Doe | 101 |
| Jane Doe | 118 |
| Will Drake | 102 |
| Caleb White | 119 |
How do you find the orders per work hour of each employee? Ideally the query should result in something like (the values are arbitrary):
| employee_id | name | shift_date | order_per_work_hour |
|---|---|---|---|
| 101 | John Doe | 01/08/2015 | 2.5 |
| 118 | Jane Doe | 01/08/2015 | 3 |
| 101 | John Doe | 01/08/2017 | 3.5 |
| 119 | Caleb White | 01/08/2017 | 1.5 |
I am aware that in order to get the order per work hour for each employee per day, you'd have to first divide total_work_in_seconds by 3600 and then use this to divide the counted orders of the employee for that specific day. The problem is I do not know how to translate the code into SQL statement. I also know that it would require three JOIN statements.
Additionally, for the order dates there are only two unique dates in the table which are 01/08/2015 and 01/08/2017.
CodePudding user response：
try:
WITH o AS (
SELECT
employee_id,
order_date,
COUNT(employee_id) AS num_orders
FROM `dataset.Orders`
GROUP BY employee_id, order_date
)
SELECT
o.employee_id,
e.name,
h.shift_date,
SAFE_DIVIDE(o.num_orders, h.total_work_in_seconds/3600) AS order_per_work_hour
FROM o
LEFT JOIN `dataset.Employee_Hours` h ON o.employee_id=h.employee_id AND o.order_date=h.shift_date
LEFT JOIN `dataset.Employees` e ON o.employee_id=e.employee_id
Nevertheless I haven't tested it because I don't have similar data in my BQ.
I supposed you need a left join because your expected output have only workers who took orders but if that is not the case you can use the type of join that better fits your needs.