Click here to Skip to main content
15,033,877 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
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

1 solution

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.
   

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