Count distinct records in a JOINed SQL query
I've modified the query to use a GROUP BY instead of a COUNT DISTINCT, which has sped up the execution time.

SELECT COUNT(*) AS totalDevices
FROM (
  SELECT d.id
  FROM Device AS d
  INNER JOIN TestResult AS r ON r.deviceId = d.id
  WHERE serialNumber BETWEEN '000000001000' AND '000000002020'
    AND r.createdAt BETWEEN '2020-03-27T11:54:43.100Z' AND '2020-04-01 09:21:02.362768'
  GROUP BY d.id
);


This query shows the following execution time with 1,000,000 records:

Run Time: real 3.433 user 0.953125 sys 2.484375


The COUNT DISTINCT query shows the following execution time:

Run Time: real 8.090 user 1.562500 sys 6.500000


So the GROUP BY query takes just under half of the time of the COUNT DISTINCT query.