Optimise web panel users statistics query

Details

Can anyone instruct me on a more optimised way of querying this statistics of registered users? I currently lack the knowledge to create a better query. Right now it works fine, but I want to learn more about other ways to do the same thing and improve on it, this way I can expand my knowledge on MySQL, and study on other functions.

Also, I'm doing everything in a single query, would it better to split it into more focused queries?

QUERY

```
SELECT
# Count users with complete profile registered current month and year
SUM(CASE WHEN completed_registration = 1 AND MONTH(created_at) = MONTH(CURDATE()) AND YEAR(created_at) = YEAR(CURDATE()) THEN 1 ELSE 0 END) AS completed_0,

# Count users with complete profile registered 1 month ago
SUM(CASE WHEN completed_registration = 1 AND MONTH(created_at) = MONTH(CURDATE() - INTERVAL 1 MONTH) AND YEAR(created_at) = YEAR(CURDATE() - INTERVAL 1 MONTH) THEN 1 ELSE 0 END ) AS completed_1,

# Count users with incomplete profile and verified email registered current month and year
SUM(CASE WHEN completed_registration = 0 AND verified_email = 1 AND MONTH(created_at) = MONTH(CURDATE()) AND YEAR(created_at) = YEAR(CURDATE()) THEN 1 ELSE 0 END) AS incomplete_0,

# Count users with incomplete profile and verified email registered 1 month ago
SUM(CASE WHEN completed_registration = 0 AND verified_email = 1 AND MONTH(created_at) = MONTH(CURDATE() - INTERVAL 1 MONTH) AND YEAR(created_at) = YEAR(CURDATE() - INTERVAL 1 MONTH) THEN 1 ELSE 0 END) AS incomplete_1,

# Count users with unverified email registered current month and year
SUM(CASE WHEN verified_email = 0 AND MONTH(created_at) = MONTH(CURDATE()) AND YEAR(created_at) = YEAR(CURDATE()) THEN 1 ELSE 0 END) AS unverified_0,

# Count users with unverified email registered 1 month ago
SUM(CASE WHEN verified_email = 0 AND MONTH(created_at) = MONTH(CURDATE() - INTERVAL 1 MONTH) AND YEAR(created_at) = YEAR(CURDATE() - INTERVAL 1 MONTH) THEN 1 ELSE 0 END) AS unve

General observations

Lines that extend to 353 columns are hard to read. You should find a code formatting style with line breaks and indentation that emphasize the structure of your query.

In many cases, you compare months and years like this:

MONTH(created_at) = MONTH(CURDATE() - INTERVAL 1 MONTH) AND YEAR(created_at) = YEAR(CURDATE() - INTERVAL 1 MONTH)

This is inefficient, because the server needs to call the MONTH() and YEAR() function on every single created_at value, and cannot take advantage of indexes. (I hope that your created_at columns are indexed.) Instead, you want to compare created_at against thresholds:

created_at BETWEEN CURDATE() - INTERVAL (DAYOFMONTH(CURDATE()) - 1) DAY - INTERVAL 1 MONTH
               AND CURDATE() - INTERVAL (DAYOFMONTH(CURDATE()) - 1) DAY - INTERVAL 1 SECOND

Sales query

Let's start with the easy decision first: four of the twenty-something SELECTs are not like the others. In fact, they have nothing to do whatsoever with the user statistics. The sales figures should clearly be split into a separate query:

SELECT SUM(purchase_total) AS total_profits
     , SUM(CASE WHEN created_at      >= CURDATE() - INTERVAL (DAYOFMONTH(CURDATE()) - 1) DAY THEN purchase_total END) AS profits_0
     , SUM(CASE WHEN created_at BETWEEN CURDATE() - INTERVAL (DAYOFMONTH(CURDATE()) - 1) DAY - INTERVAL 1 MONTH
                                    AND CURDATE() - INTERVAL (DAYOFMONTH(CURDATE()) - 1) DAY - INTERVAL 1 SECOND THEN purchase_total END) AS profits_1
     , COUNT(*) AS total_sales
    FROM purchases
    WHERE transaction_status = 'completed';

User summary query

Another five SELECTs are distinguished by the fact that they are timeless system-wide totals of certain categories of users. This should be another separate query.

SELECT COUNT(*) AS total_users
     , COUNT(CASE WHEN completed_registration = 1 THEN 1 END) AS total_registered_users
     , COUNT(CASE WHEN completed_registration = 0 THEN 1 END) AS total_incomplete_users
     , COUNT(CASE WHEN verified_email = 0 THEN 1 END) AS total_unverified_users
     , (SELECT COUNT(*) FROM clients) AS total_clients
    FROM users;

Arguably, total_clients should be further split into a standalone query.

This month vs. last month query

SQL excels at working with sets of data. However, when you create a table with one row and twenty columns, you don't really have a data set anymore — you have twenty scalar values. In that form, you can only address the twenty values individually, not collectively.

Therefore, I recommend transposing the table, such that it is tall rather than wide. Let's start by creating a view:

CREATE OR REPLACE VIEW user_events AS
    SELECT 'Complete profile registered' AS type, created_at
        FROM users
        WHERE completed_registration = 1
    UNION ALL
    SELECT 'Incomplete profile and verified email', created_at
        FROM users
        WHERE completed_registration = 0 AND verified_email = 1
    UNION ALL
    SELECT 'Unverified email registered this month', created_at
        FROM users
        WHERE verified_email = 0

    UNION ALL

    SELECT 'Onsite Teachers registered', created_at
        FROM onsite_teachers
    UNION ALL
    SELECT 'Onsite Teachers hired', created_at
        FROM purchases
            INNER JOIN purchased_profiles
                ON purchased_profiles.purchase_id = purchases.id
                AND purchased_profiles.profile_type = 'onsite_teacher'
        WHERE purchases.transaction_status = 'completed'
    UNION ALL
    SELECT 'Translators registered', created_at
        FROM translators
    UNION ALL
    SELECT 'Translators hired', created_at
        FROM purchases
            INNER JOIN purchased_profiles
                ON purchased_profiles.purchase_id = purchases.id
                AND purchased_profiles.profile_type = 'translator'
        WHERE purchases.transaction_status = 'completed'
    UNION ALL
    SELECT 'Interpreters registered', created_at
        FROM interpreters
    UNION ALL
    SELECT 'Interpreters hired', created_at
        FROM purchases
            INNER JOIN purchased_profiles
                ON purchased_profiles.purchase_id = purchases.id
                AND purchased_profiles.profile_type = 'interpreter'
        WHERE purchases.transaction_status = 'completed';

Then we can obtain monthly aggregate counts:

SELECT type
     , COUNT(CASE WHEN created_at      >= CURDATE() - INTERVAL (DAYOFMONTH(CURDATE()) - 1) DAY THEN 1 END) AS this_month
     , COUNT(CASE WHEN created_at BETWEEN CURDATE() - INTERVAL (DAYOFMONTH(CURDATE()) - 1) DAY - INTERVAL 1 MONTH
                                      AND CURDATE() - INTERVAL (DAYOFMONTH(CURDATE()) - 1) DAY - INTERVAL 1 SECOND THEN 1 END) AS last_month
    FROM user_events
    GROUP BY type;

StackExchange Code Review Q#77466, answer score: 4