Knowee
Questions
Features
Study Tools

You will be working with a table named job_data with the following columns:job_id: Unique identifier of jobsactor_id: Unique identifier of actorevent: The type of event (decision/skip/transfer).language: The Language of the contenttime_spent: Time spent to review the job in seconds.org: The Organization of the actords: The date in the format yyyy/mm/dd (stored as text).Tasks:Jobs Reviewed Over Time:Objective: Calculate the number of jobs reviewed per hour for each day in November 2020.Your Task: Write an SQL query to calculate the number of jobs reviewed per hour for each day in November 2020

Question

You will be working with a table named job_data with the following columns:job_id: Unique identifier of jobsactor_id: Unique identifier of actorevent: The type of event (decision/skip/transfer).language: The Language of the contenttime_spent: Time spent to review the job in seconds.org: The Organization of the actords: The date in the format yyyy/mm/dd (stored as text).Tasks:Jobs Reviewed Over Time:Objective: Calculate the number of jobs reviewed per hour for each day in November 2020.Your Task: Write an SQL query to calculate the number of jobs reviewed per hour for each day in November 2020

...expand
🧐 Not the exact question you are looking for?Go ask a question

Solution 1

Sure, here is the SQL query to calculate the number of jobs reviewed per hour for each day in November 2020:

SELECT 
    DATE(ds) AS date,
    EXTRACT(HOUR FROM ds) AS hour,
    COUNT(job_id) AS jobs_reviewed
FROM 
    job_data
WHERE 
    ds >= '2020-11-01' AND ds < '2020-12-01'
GROUP BY 
    date, hour
ORDER BY 
    date, hour;

This query works as follows:

  1. The DATE(ds) function extracts the date part from the ds column.
  2. The EXTRACT(HOUR FROM ds) function extracts the hour part from the ds column.
  3. The COUNT(job_id) function counts the number of jobs reviewed.
  4. The WHERE clause filters the data to include only the jobs reviewed in November 2020.
  5. The GROUP BY clause groups the data by date and hour.
  6. The ORDER BY clause sorts the result by date and hour.

This problem has been solved

Solution 2

Sure, here is the SQL query to calculate the number of jobs reviewed per hour for each day in November 2020:

SELECT 
    DATE(ds) AS date,
    EXTRACT(HOUR FROM ds) AS hour,
    COUNT(job_id) AS jobs_reviewed
FROM 
    job_data
WHERE 
    ds BETWEEN '2020-11-01' AND '2020-11-30'
    AND event = 'decision'
GROUP BY 
    date,
    hour
ORDER BY 
    date,
    hour;

This query works as follows:

  1. The DATE(ds) function is used to convert the ds column to a date format.
  2. The EXTRACT(HOUR FROM ds) function is used to get the hour from the ds column.
  3. The COUNT(job_id) function is used to count the number of jobs reviewed.
  4. The WHERE clause is used to filter the data for November 2020 and only include 'decision' events.
  5. The GROUP BY clause is used to group the data by date and hour.
  6. The ORDER BY clause is used to sort the data by date and hour.

This problem has been solved

Similar Questions

Write an SQL query to calculate the number of jobs reviewed per hour for each day in November 2020.

a) With examples distinguish between the following terms as used in Event driven programming;i. Entity (2 marks)ii. File (2 marks)iii. Record (2 marks)iv. Table (2 marks)b) A program is required to determine the gross pay for each of the several employees of acertain company. The company pays “straight-time” for the first 40 hours worked by eachemployee and pays “one-and a half time” for all hours worked in excess of 40 hours. You aregiven a list of employees in the company, the number of hours each employee worked and thehourly rate of each employee. You are to design a program in which you should input thisinformation for each employee, and then compute and display the employee’s gross pay. use textboxes for both inputs and outputs.Required:i. Design the graphical user interface for this program (4 marks)ii. Write a program code to compute the gross pay. Your program should validate the inputsin such a way that the names of the employees must be input, the number of hoursworked and pay rate must be numeric, greater than zero but less than or equal to 60 andgreater than zero respectively. Display an appropriate message in case of violation.(8 marks

It captures the time that individual workers spend on each production job.Select one:a.labor distribution summaryb.Personnel Action Formsc.time cardd.job ticket

With examples distinguish between the following terms as used in Event driven programming;i. Entity (2 marks)ii. File (2 marks)iii. Record (2 marks)iv. Table (2 marks)b) A program is required to determine the gross pay for each of the several employees of acertain company. The company pays “straight-time” for the first 40 hours worked by eachemployee and pays “one-and a half time” for all hours worked in excess of 40 hours. You aregiven a list of employees in the company, the number of hours each employee worked and thehourly rate of each employee. You are to design a program in which you should input thisinformation for each employee, and then compute and display the employee’s gross pay. use textboxes for both inputs and outputs.Required:i. Design the graphical user interface for this program (4 marks)ii. Write a program code to compute the gross pay. Your program should validate the inputsin such a way that the names of the employees must be input, the number of hoursworked and pay rate must be numeric, greater than zero but less than or equal to 60 andgreater than zero respectively. Display an appropriate message in case of violation

A list of tasks, duties, and responsibilities of a job resulting from the stage of job analysis is called a _____.Group of answer choicesjob protocolstandard operating procedurejob descriptionreferral reportjob announcement

1/1

Upgrade your grade with Knowee

Get personalized homework help. Review tough concepts in more detail, or go deeper into your topic by exploring other relevant questions.