Super Challenging SQL query to solve

[sana]

Table Name: food_test

`    person_id     food_sample_id     heart_rate`
         1                     1        120
         1                     5        125
         1                     3        130
         2                     1        71
         3                     2        85
         4                     3        70
         4                     5        90

indent preformatted text by 4 spaces

Question: Write a query to display person_id, food_sample_id, heart_rate, average_heart_rate, difference between heart_rate and their average heart_rate as variation for people who have tried more than one food sample.

Result data set should look something like this:

person_id      food_sample_id   heart_rate      average_heart_rate     variation
     1             1                120                    125           -5
     1             5                125                    125            0
     1             3                130                    125            5
     4             3                 70                     80           -10
     4             5                 90                     80            10

Challenge:

Fill in the blanks: (Use the same format as below. Only fill in the blanks.)

SELECT person_id, food_sample_id, heart_rate, [a]______
FROM food_test
WHERE [b]________;

[yosiasz]

Hello Sana

What have you done so far with this problem?

[sana]

Have been able to do it using aliases and joins, but couldn't make it fit into the format above.

[yosiasz]

Please show us what you have done and the challenge you are facing

[sana]

SELECT T1.person_id, T1.food_sample_id, T1.heart_rate, T2.average_heart_rate,
(T1.heart_rate - T2.average_heart_rate) as variation
from food_test T1,
(SELECT person_id, AVG(heart_rate) as average_heart_rate from food_test
group by person_id having count(person_id > 1)) as T2
WHERE T1.person_id = T2.person_id
and T1.person_id in
(select person_id from food_test group by person_id having count(person_id) >1);

This one doesn't follow the format.

What else I've come up with:

select person_id, food_sample_id, heart_rate, ___________
from food_test
where person_id in (select person_id from food_test group by person_id having count(person_id) > 1);

[bitsmed]

[a] avg(heart_rate) over(partition by person_id),heart_rate-avg(heart_rate) over(partition by person_id)

[sana]

over (partition...) doesn't work for the query..

[bitsmed]

Which database engine and version are you using?

[sana]

This seems to work to some extent:

SELECT person_id, food_sample_id, heart_rate, AVG(heart_rate), heart_rate - AVG(heart_rate)
FROM food_test
where person_id in (SELECT person_id from food_test group by person_id having count(person_id) > 1)
GROUP BY person_id;

But it is grouping the persons and showing only one record for 1st person, 1 record for 4th person.

How do I group the table by column but still display data along with grouped data for all rows?

[bitsmed]

avg(…) over(…) should work, depending on which version of Microsoft SQL Server you are running (that was actually why I asked in previous post).

[harishgg1]

hi

i tried to do this

hope it helps

drop create data ....

drop table food_test 
 go 
 
 create table food_test 
 (
 person_id    int ,
 food_sample_id   int ,  
 heart_rate int 
 )
 go 
 
 insert into food_test select          1 ,                     1 ,       120
 insert into food_test select          1 ,                     5 ,       125
 insert into food_test select          1 ,                     3 ,       130
 insert into food_test select          2 ,                     1 ,       71
 insert into food_test select          3 ,                     2 ,       85
 insert into food_test select          4 ,                     3 ,       70
 insert into food_test select          4 ,                     5 ,       90
 go

SQL...

SELECT b.person_id, 
       b.food_sample_id, 
       b.heart_rate, 
       a.avghr, 
       b.heart_rate - a.avghr 
FROM   (SELECT person_id, 
               Avg(heart_rate) AS avghr 
        FROM   food_test 
        GROUP  BY person_id) a, 
       food_test b 
WHERE  [b].person_id = [a].person_id

[Lewie]

@sana

for people who have tried more than one food sample.

not sure what they exactly mean by this but you may have to add to the above query: -
SELECT b.person_id,
b.food_sample_id,
b.heart_rate,
a.avghr,
b.heart_rate - a.avghr
FROM (SELECT person_id,
Avg(heart_rate) AS avghr
FROM food_test
GROUP BY person_id
HAVING COUNT(*) > 1) a,
food_test b
WHERE [b].person_id = [a].person_id

[sana]

The challenge was to put it into this format:

SELECT person_id, food_sample_id, heart_rate, [a] ______
FROM food_test
WHERE [b] ________;

You cannot change what's already there. You can only fill in the blanks [a] and [b].

[sana]

It is MySQL Server 5.7

[Lewie]

CREATE TABLE #food_test
  (
     person_id      INT,
     food_sample_id INT,
     heart_rate     INT
  )

go

INSERT INTO #food_test
SELECT 1,
       1,
       120

INSERT INTO #food_test
SELECT 1,
       5,
       125

INSERT INTO #food_test
SELECT 1,
       3,
       130

INSERT INTO #food_test
SELECT 2,
       1,
       71

INSERT INTO #food_test
SELECT 3,
       2,
       85

INSERT INTO #food_test
SELECT 4,
       3,
       70

INSERT INTO #food_test
SELECT 4,
       5,
       90

go

SELECT person_id,
       food_sample_id,
       heart_rate,
       (SELECT Sum(heart_rate) / Count(*)
        FROM   #food_test a
        WHERE  a.person_id = #food_test.person_id)              AS
       average_heart_rate,
       heart_rate - (SELECT Sum(heart_rate) / Count(*)
                     FROM   #food_test a
                     WHERE  a.person_id = #food_test.person_id) AS variation
FROM   #food_test
WHERE  EXISTS (SELECT Count(*)
               FROM   #food_test A
               WHERE  a.person_id = #food_test.person_id
               GROUP  BY person_id
               HAVING Count(*) > 1) 

[Lewie]

or

SELECT person_id, food_sample_id, heart_rate,
avg(heart_rate) OVER (PARTITION BY person_id) AS average_heart_rate,
heart_rate - AVG(heart_rate) OVER (PARTITION BY person_id) AS Variation
FROM #food_test
WHERE EXISTS (SELECT COUNT() FROM #food_test A WHERE a.person_id = #food_test.person_id GROUP BY person_id HAVING COUNT() > 1)

[bitsmed]

I (and most of this comunity) work with Microsoft SQL Server.
My knowledge on MySQL goes years back, so I'm a bit rusty. I know that MySQL introduced over(partition by …) in the version 8 (the version after the one you're running), so if you have the option of upgrading, my first post in this thread should work.

Otherwise you might get better answers, asking in a forum dedicated for MySQL.