Query to get daily visitor count as login history (MySQL)

There is a table that takes the user ID (INT) and login time (Timestamp) as columns, and INSERTs the record once every time the user logs in.

I'd like to get the number of duplicate daily visitors from this table, but strangely, I can't think of any queries<

I tried many things

First of all,

 SELECT A, COUNT (*) FROM (SELECT DATE) ASA, User ID FROM Login History Table GROUP BYA, User ID) ASB GROUP BYA

I tried it this way, but maybe it takes about 15 minutes to perform the query because there are about 20 million ROWs.

I'm wondering if there's a faster and more efficient query that produces the same result from this table because I think it'll put a lot of load on the DB if it's a useless operation.

Is there a standard way to solve this problem?

mysql querying sql database

2022-09-22 14:51

2 Answers

Getting the number of visitors

You didn't tell me what the backend is, so I'm going to use the node.js as a reference link.

2022-09-22 14:51

 SELECT DATE_FORMAT (login time, %Y-%m-%d') AS daily, 
        COUNT (DISTINCT User ID) AS users
    FROM Login History Table GROUP BY DAILY

Wouldn't this work?

2022-09-22 14:51

If you have any answers or tips

Popular Tags

python x 4647

android x 1593

java x 1494

javascript x 1427

c x 927

c++ x 878

ruby-on-rails x 696

php x 692

python3 x 685

html x 656