I've been working on solving the problem shown here, and a question arose
Here's the schema I created, my statement of the problem, and my test data:
CREATE TABLE Students (group_id text, sql_quotient float); INSERT INTO Students(group_id, sql_quotient) VALUES ( 'A', 25 ), ( 'B', 30 ), ( 'C', 40 ), ( 'A', 35 ), ( 'B', 20 );
Task: Display max average
sql_quotient among all the groups.
group_idis guaranteed to be a Single character of range from A-Z.
- Here for group A, avg is 30; for B, avg is 25; and for C, avg is 40; hence, 40 should be displayed.
I tried the following 2 queries; both give me the right answer.
select max(round(b.avg_quotient,2)) as answer from (SELECT AVG(sql_quotient) as avg_quotient FROM Students GROUP BY group_id) as b;
Runtime = 0.002378 sec
select max(round(b.avg_quotient,2)) as answer from (SELECT AVG(sql_quotient) as avg_quotient FROM Students GROUP BY substr(group_id,1,1) )as b;
Runtime = 0.000459 sec
The difference - the first query groups the data by
group_id; the second by `substr(group_id,1,1).
As the second query applies an additional function, I would expect it to take longer. However, as you can see above, runtime of query no.2 is remarkably less than query no. 1.
My question: why does query 2 have a lower runtime than query 1, even though query 2 has one extra function (substr()).
- The schema is already defined. I don't know why the datatype of Id is text instead of char(1), and for the purposes of my question it's irrelevant.
- I'm looking for the reason for the difference of runtime between these 2 queries, not the another query for same problem.
- The Students table is created anew for each run, so this isn't a case of the data having to be read from disk on the first run, and being in memory on the second. To prove this, I ran the queries four more times, running Query 2, then Query 1, then 2 again, then 1 again. The run times were:
- Q2, 1st: 0.000493
- Q1, 1st: 0.002779
- Q2, 2nd: 0.000499
- Q1, 2nd: 0.002787