Answer to Question #185020 in Databases | SQL | Oracle | MS Access for bhadra

Question

Best time to upload a comedy video:

DunkFest channel is planning to upload a video in the "COMEDY" genre

Give the channel the best suitable hour of the day when users positively engage more with comedy videos.

Note:

• Consider positive engagement as LIKE for the videos in the "COMEDY" genre_type.
• Consider reaction_type LIKE as liked.
• Return the hour in the integer format

Expected Output Format

hour_of_engagementno_of_likes5

sir plz explain sql query

Answer 1

/* 

Assuming we have a table named comedy which stores the number of Hours and Engagements made by users.

with

- the hours are stored with data type TIME(0) and Engagement, of type VARCHAR(8).

And the table has some data for example; 

Engagement Hours

LIKE 09:40:00

LIKE 08:50:01

LIKE 08:50:01

LIKE 07:09:40

LIKE 09:40:00

LIKE 08:50:01

LIKE 08:50:01

LIKE 07:09:40

Not LIKE 09:40:00

LIKE 10:50:01

LIKE 10:50:01

LIKE 10:50:01

To get the best suitable hour of the day when users positively engage more with comedy videos,

We have to find the most repeated hours from the table where the engagement is equal to LIKE. 

So in our query, 

- We will first select the hours from the comedy table where the Engagement is equal to LIKE

e.g; 

SELECT Hours

FROM COMEDY

WHERE Engagement = 'LIKE'

- then GROUP BY the result to summarize the rows with same values. 

SELECT Hours

FROM COMEDY

WHERE Engagement = 'LIKE'

GROUP BY Hours

- we then select the rows reapted more than once, and use the ccount function to the number of times the hours are repeated. 

SELECT Hours

FROM COMEDY

WHERE Engagement = 'LIKE'

GROUP BY Hours

HAVING COUNT(Hours)>1

- Lastly, we order the results in Descending order and only return the first observation - this is the best time of the day. i.e, the most repeated time.

SELECT Hours

FROM COMEDY

WHERE Engagement = 'LIKE'

GROUP BY Hours

HAVING COUNT(Hours)>1

ORDER BY COUNT(Hours)

DESC LIMIT 1

- Since we are asked to return the hours in integer format and as hour_of_engagementno_of_likes5, we will use the CAST & REPLACE function to convert the hours as integers, and rename the column to your name, hour_of_engagementno_of_likes5

SELECT CAST(REPLACE(Hours,':','') AS INT) As hour_of_engagementno_of_likes5

To put everything together; 

-- final 

SELECT CAST(REPLACE(Hours,':','') AS INT) As hour_of_engagementno_of_likes5

FROM COMEDY

WHERE Engagement = 'LIKE'

GROUP BY Hours 

HAVING COUNT(Hours)>1

ORDER BY COUNT(Hours)

DESC LIMIT 1

*/ 

-- final 

SELECT CAST(REPLACE(Hours,':','') AS INT) As hour_of_engagementno_of_likes5

FROM COMEDY

WHERE Engagement = 'LIKE'

GROUP BY Hours 

HAVING COUNT(Hours)>1

ORDER BY COUNT(Hours)

DESC LIMIT 1