Optimize SQL for Large Tables

You're a QA Engineer at Gen Digital (maker of Norton, Avast and LifeLock), sanity-checking a reporting query before it ships to the licensing dashboard team. The table license_activations holds roughly 10 million rows of subscription activation events, and this is the query a data engineer wants you to sign off on.

CREATE TABLE license_activations (
    activation_id BIGINT PRIMARY KEY,
    device_id BIGINT NOT NULL,
    product_sku VARCHAR(20) NOT NULL,
    activated_at TIMESTAMP NOT NULL,
    country_code CHAR(2) NOT NULL,
    status VARCHAR(20) NOT NULL
);
-- Indexes: idx_activated_at(activated_at), idx_device_id(device_id), idx_product_sku(product_sku)

SELECT DISTINCT
    la.device_id,
    la.product_sku,
    la.country_code,
    (SELECT COUNT(*) FROM license_activations la2
     WHERE la2.device_id = la.device_id) AS activation_count
FROM license_activations la
WHERE YEAR(la.activated_at) = 2024
  AND la.product_sku LIKE '%PRO'
  AND la.status = 'active'
ORDER BY activation_count DESC;

On a 10-million-row table this query times out in staging before it ever finishes. Explain, in plain language, why it is slow and what you would change to make it fast without changing what it returns.