1. Home
2. php - MySQL find top x% across a given time period

245 votes

1 answers

php - MySQL find top x% across a given time period

Get the solution ↓↓↓

Solution:

I will give you some pseudo sql just to show the idea I would use, you can add the missing bits and get it working. It assumes that the top 5% is based in the number of posts, but if it was based in the score of likes plus views, should be trivial to adjust.

SET @foo=0.05 * select count(*) from posts where (timestamp < today - 24 hours); 
PREPARE STMT FROM 'SELECT posts,sum(likes) * 2 + sum(views) as POPULAR FROM (tables joined) ORDER BY popular LIMIT ?';
EXECUTE STMT USING @foo;
Copy code

Regards,

Undefined asked

2023-03-31

Write your answer

---

457

votes

Answer

Solution:

I can't test this, as I don't have a MySQL server accessible right now - but the below query should get you started:

SELECT p.id, p.title, ((COUNT(l.id) * 2) + COUNT(v.id)) AS popularity 
    FROM posts p 
        LEFT JOIN posts_likes l ON l.post_id = p.id 
        LEFT JOIN posts_views v ON v.post_id = p.id 
    WHERE p.timestamp > (UNIX_TIMESTAMP()-86400)
    GROUP BY p.id
    ORDER BY popularity DESC LIMIT :return_limit
Copy code

When you run the query, you'll need to pass a parameter:return_limit - Which should be 5% of theCOUNT() of all posts in the past 24 hours.

Write your answer

---

Share solution ↓

Additional Information:

Date the issue was resolved:

2023-03-31

Link To Source
Link To Answer People are also looking for solutions of the problem: php_network_getaddresses: getaddrinfo failed: temporary failure in name resolution

Didn't find the answer?

Our community is visited by hundreds of web development professionals every day. Ask your question and get a quick answer for free.

Ask a Question

---

Similar questions

Find the answer in similar questions on our website.

766 php - How to send multiple text field values to next pho page when the text field is created using while loop?

978 phpoffice - How do I extract the text content from a word document with PHP?

226 php - import excel file to mysql with laravel 5.3

166 Should PHP exception try/catch blocks be specific to the exception?

851 php - Regex to match URL except for some websites using Look Ahead

119 php - MySql List all items from table 1 where item conditionally exists in table 2

980 php - How to convert UTC timezone to user-defined Timezone

652 preg match all - Extract Text from a string in PHP

3 php - How to split an XML file into three new arrays based on one parameter?

60 apache - Best practices for using PHP's exit from FastCGI

Write quick answer

Do you know the answer to this question? Write a quick response to it. With your help, we will make our community stronger.

About the technologies asked in this question

PHP

PHP (from the English Hypertext Preprocessor - hypertext preprocessor) is a scripting programming language for developing web applications. Supported by most hosting providers, it is one of the most popular tools for creating dynamic websites. The PHP scripting language has gained wide popularity due to its processing speed, simplicity, cross-platform, functionality and distribution of source codes under its own license.
https://www.php.net/

MySQL

DBMS is a database management system. It is designed to change, search, add and delete information in the database. There are many DBMSs designed for similar purposes with different features. One of the most popular is MySQL. It is a software tool designed to work with relational SQL databases. It is easy to learn even for site owners who are not professional programmers or administrators. MySQL DBMS also allows you to export and import data, which is convenient when moving large amounts of information.
https://www.mysql.com/