Is it possible to implement this complex query using SQL SELECT statements?

April 10, 2022

To prevent bulk registration (the site uses a WordPress program), I installed a "Register IPs" plugin to record user registration IPs. This plugin adds additional data to a new user’s wp_usermeta data under the signup_ip key.

I don’t know much about SQL SELECT statement, is it possible to use SQL SELECT statement to get all registered IPs and sort them by the number of IP appearances? For example, query the 10 most registered IPs, so that I can find suspicious IPs and block them in time.

This is my current attempt and I don’t know how to sort by IP occurrences. Any help, thanks in advance!

SELECT * 
FROM wp_usermeta 
WHERE meta_key = 'signup_ip' 
ORDER BY meta_value ASC 
LIMIT 0, 10;

>Solution :

You may aggregate by the user_id and then pivot out the IP address for use in sorting.

SELECT ip, COUNT(*) AS cnt
FROM
(
    SELECT MAX(CASE WHEN meta_key = 'signup_ip' THEN meta_value END) AS ip
    FROM wp_usermeta
    GROUP BY user_id
) t
GROUP BY ip
ORDER BY cnt DESC
LIMIT 10;