Click here to Skip to main content
15,887,683 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
See more:
Like this question is literally very confusing me.

-job_id: unique identifier of jobs
-actor_id: unique identifier of actor
-event: decision/skip/transfer
-language: language of the content
-time_spent: time spent to review the job in seconds
-org: organization of the actor,
-ds: date in the yyyy/mm/dd format. It is stored in the form of text and we use presto to run. no need for date function

CREATE TABLE job_data
(
	ds DATE,
    job_id INT NOT NULL,
    actor_id INT NOT NULL,
    event VARCHAR(15) NOT NULL,
    language VARCHAR(15) NOT NULL,
    time_spent INT NOT NULL,
    org CHAR(2)
);


ds job_id actor_id event language time_spent org
------------------------------------------------------------------
2020-11-30 21 1001 skip English 15 A
2020-11-30 22 1006 transfer Arabic 25 B
2020-11-29 23 1003 decision Persian 20 C
2020-11-28 23 1005 transfer Persian 22 D
2020-11-28 25 1002 decision Hindi 11 B
2020-11-27 11 1007 decision French 104 D
2020-11-26 23 1004 skip Persian 56 A
2020-11-25 20 1003 transfer Italian 45 C

And this the table from which we have to count. Points to be considered :
What does the event mean? What to consider for reviewing?

What I have tried:

SELECT COUNT(*) AS no_of_job, ds AS dates 
FROM job_data 
GROUP BY ds 
ORDER BY no_of_job DESC;
Posted
Updated 14-Sep-21 20:08pm

You can't: your data does not contain any timestamp which would allow you to identify the time of day that the job was reviewed, and since "per hour per day" is a requirement without that information there is nothing you can do to fulfil it.

I suspect that either your data design is flawed - which means your data is useless for this task - or the task is badly stated and you should go back to whoever gave it to you and get the exact requirement clarified, preferably with sample input and output data.
 
Share this answer
 
this works alright in MYSQL , provided data may be incomplete, but here's what i did hoping it would work for some project etc!!
---
SQL
SELECT ds AS Dated, COUNT(job_id) AS job_review_counts,  ROUND(COUNT(job_id)/(SUM(time_spent)/(60*60)),2) AS job_review_per_hour_each_day  
FROM job_data
WHERE ds BETWEEN '01-11-2020' AND '30-11-2020'
GROUP BY ds 
ORDER BY ds

---
OUTPUT IN MYSQL

11/25/2020 1 80.00
11/26/2020 1 64.29
11/27/2020 1 34.62
11/28/2020 2 218.18
11/29/2020 1 180.00
11/30/2020 2 180.00

---
 
Share this answer
 
Comments
Dave Kreskowiak 22-Mar-24 15:36pm    
You seem to have missed the "per hour per day" requirement. The query isn't doable as there is nothing in the data that specifies what time the request was examined.

At least try to read the other answers if you're going to answer old questions.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900