Three Different MySQL Solutions + One Unique Solution (Faster Than 97%)
Setup\n\n1. What version of MySQL are we working with?\n\n   sql\n   SELECT VERSION() AS \'version\';\n\n   #=>\n\n   +--------+\n   | version|\n   +--------+\n
## Setup\n\n1. What version of MySQL are we working with?\n\n   ```sql\n   SELECT VERSION() AS \'version\';\n\n   #=>\n\n   +--------+\n   | version|\n   +--------+\n   | 8.0.21 |\n   +--------+\n   ```\n\n   Now we know what MySQL reference to use in the future: 8.0, found [here][1].\n\n2. What database should we access?\n\n   ```sql\n   SHOW DATABASES;\n\n   #=>\n\n   +--------------------+\n   | Database           |\n   +--------------------+\n   | information_schema |\n   | test               |\n   +--------------------+\n   ```\n\n   Since `information_schema` just provides MySQL instance metadata (more on that [here][2]). We will be working with the `test` database.\n\n3. The problem description mentions the `employee` table for the `test` database.  We can check that with:\n\n   ```sql\n   SHOW TABLES IN test;\n   \n   #=>\n   \n   +----------------+\n   | Tables_in_test |\n   +----------------+\n   | employees      |\n   +----------------+\n   ```\n\nGreat!  We are all set.\n\n## Solutions\n\n### Subtract First\n\nSubtract `test.employees.in_time` from `test.employees.out_time` before applying the `SUM()` function:\n\n```sql\nSELECT test.employees.event_day AS \'day\',\n  test.employees.emp_id AS \'emp_id\',\n  SUM(test.employees.out_time - test.employees.in_time) AS \'total_time\'\n  FROM test.employees\n  GROUP BY test.employees.event_day,\n    test.employees.emp_id\n  LIMIT 1000;\n\n#=>\n\n+------------+--------+------------+\n| day        | emp_id | total_time |\n+------------+--------+------------+\n| 2020-11-28 | 1      | 173        |\n| 2020-12-03 | 1      | 41         |\n| 2020-11-28 | 2      | 30         |\n| 2020-12-09 | 2      | 27         |\n+------------+--------+------------+\n```\n\nwhich gave me a query time of roughly 800ms.\n\n**Note**: while the `LIMIT` clause here is unnecessary, it\'s a great habit to get into when working with staging and production databases.\n\n### `SUM()` First\n\n```sql\nSELECT test.employees.event_day AS \'day\',\n  test.employees.emp_id AS \'emp_id\',\n  SUM(test.employees.out_time) - SUM(test.employees.in_time) AS \'total_time\'\n  FROM test.employees\n  GROUP BY test.employees.event_day,\n    test.employees.emp_id;\n\n#=>\n\n+------------+--------+------------+\n| day        | emp_id | total_time |\n+------------+--------+------------+\n| 2020-11-28 | 1      | 173        |\n| 2020-12-03 | 1      | 41         |\n| 2020-11-28 | 2      | 30         |\n| 2020-12-09 | 2      | 27         |\n+------------+--------+------------+\n```\n\nThis optimization takes the 800ms query time down to 450ms for me.\n\n### Common Table Expression (CTE)\n\nA Common Table Expression (CTE) is a `WITH` clause appearing before the `SELECT` statement.  A CTE is meant to reduce multiple expressions into a single reusable expression.  You can find more about the `WITH` clause and CTE [here][3].\n\nWhile we are only subtracting (`-`) once for this query, the CTE increases complexity:\n\n```sql\nWITH\n  common_table_expression AS (SELECT test.employees.emp_id AS \'emp_id\',\n                                test.employees.event_day AS \'event_day\',\n                                test.employees.out_time - test.employees.in_time AS \'time\'\n                                FROM test.employees)\nSELECT common_table_expression.event_day AS \'day\',\n  common_table_expression.emp_id AS \'emp_id\',\n  SUM(common_table_expression.time) AS \'total_time\'\n  FROM common_table_expression\n  GROUP BY common_table_expression.event_day,\n    common_table_expression.emp_id;\n\n#=>\n\n+------------+--------+------------+\n| day        | emp_id | total_time |\n+------------+--------+------------+\n| 2020-11-28 | 1      | 173        |\n| 2020-12-03 | 1      | 41         |\n| 2020-11-28 | 2      | 30         |\n| 2020-12-09 | 2      | 27         |\n+------------+--------+------------+\n```\n\nwhile reducing query time to 450ms for me.\n\n### Combination Solution\n\nWhat if we combined the optimization of `SUM()` before subtraction (`-`) _and_ a CTE?\n\n```sql\nWITH\n  common_table_expression AS (SELECT test.employees.emp_id AS \'emp_id\',\n                                test.employees.event_day AS \'event_day\',\n                                SUM(test.employees.out_time) AS \'out_time\',\n                                SUM(test.employees.in_time) AS \'in_time\'\n                                FROM test.employees\n                                GROUP BY test.employees.event_day,\n                                  test.employees.emp_id)\nSELECT common_table_expression.event_day AS \'day\',\n  common_table_expression.emp_id AS \'emp_id\',\n  common_table_expression.out_time - common_table_expression.in_time AS \'total_time\'\n  FROM common_table_expression;\n\n#=>\n\n+------------+--------+------------+\n| day        | emp_id | total_time |\n+------------+--------+------------+\n| 2020-11-28 | 1      | 173        |\n| 2020-12-03 | 1      | 41         |\n| 2020-11-28 | 2      | 30         |\n| 2020-12-09 | 2      | 27         |\n+------------+--------+------------+\n```\n\nWhile your results may vary, I was able to get just above 400ms for this one: faster than 97% of all queries!\n\n[1]: dev.mysql.com/doc/refman/8.0/en\n[2]: dev.mysql.com/doc/refman/8.0/en/information-schema.html\n[3]: dev.mysql.com/doc/refman/8.0/en/with.html\n
['MySQL']