title: Q: Efficiently get the oldest value, most recent value and count per group in large table?
body: <p>I am looking to improve my query performance / or a more efficient query design to get the min, max and count per group in a mysql table.</p>
  
  <p>My required output is:</p>
  
  <pre><code>+---------+----------+----------+-----+
  |      id | f_amount | r_amount | cnt |
  +---------+----------+----------+-----+
  |       1 |     1000 |      200 |   3 |
  |       2 |      300 |      300 |   1 |
  |       3 |      450 |      600 |   2 |
  +---------+----------+----------+-----+
  </code></pre>
  
  <p>where f_amount is the oldest amount, r_amount is the most recent amount and cnt is the number of transactions for that particular id.</p>
  
  <p>My query [which gets the desired result but is extremely slow]. My table has close to 1 billion records and each id itself has thousands of transactions and all the data is in MySQL.</p>
  
  <p>I am not able to implement the same using a Common Table Expression.</p>
  
  <pre><code>SELECT     x.fund_id AS id, 
             min_amt AS f_amount, 
             max_amt AS r_amount, 
             z.cnt 
  FROM       ( 
                    SELECT fund_id, 
                           amount AS min_amt, 
                           dt 
                    FROM   trans
                    WHERE  dt = 
                           ( 
                                  SELECT Min(dt) 
                                  FROM   trans g 
                                  WHERE  g.fund_id = trans.fund_id)) x 
  INNER JOIN 
             ( 
                    SELECT fund_id, 
                           amount AS max_amt, 
                           dt 
                    FROM   trans
                    WHERE  dt = 
                           ( 
                                  SELECT Max(dt) 
                                  FROM   trans g 
                                  WHERE  g.fund_id = trans.fund_id)) y 
  INNER JOIN 
             ( 
                      SELECT   fund_id, 
                               Count(fund_id) AS cnt 
                      FROM     trans g 
                      GROUP BY 1) z 
  where      x.fund_id = y.fund_id 
  AND        x.fund_id = z.fund_id 
  ORDER BY   x.fund_id;
  </code></pre>
  
  <p>Table creation and sample data insert:</p>
  
  <pre><code>CREATE TABLE trans (
    fund_id int,
    amount int,
    dt date);
  
  
  
  insert into trans values(1,1000,'2019-02-01');
  insert into trans values(1,500,'2019-02-02');
  insert into trans values(1,200,'2019-02-03');
  insert into trans values(2,300,'2019-02-15');
  insert into trans values(3,450,'2019-02-17');
  insert into trans values(3,600,'2019-02-20');
  </code></pre>