-1

I've seen other solutions. Curious if this method of solving these would work.

TABLES:

attendance_events : date | student_id | attendance

all_students : student_id | school_id | grade_level | date_of_birth | hometown

What percent of students attend school on their birthday?

With agg_join as (SELECT att.date as dates, att.attendance as attendance, als.date_of_birth as DOB, att.student_id as student_id
      FROM attendance_events att
      join all_students als on att.student_id = als.studentid)
      
Select count(DISTINCT student_id) as total_students, 
  count( Distinct case when DOB = dates and attendance = TRUE) as count_of_DOBS,
  total_students/ count_of_DOBS as percent_of_student
from agg_join

Which grade level had the largest drop in attendance between yesterday and today?

With agg_join as ( SELECT att.date as dates, att.attendance as attendance, als.grade_level as grade
      FROM attendance_events att
      join all_students als on att.student_id = als.studentid)
      
Select grade,  
      case when dates ( 'd', -1, currentdate) and attendance = True then 1
      else 0 end as yesterday_att,

       case when dates ( 'd', currentdate) and attendance = True then 1
      else 0 end as Today_att, 
      
      (Today_att - yesterday_att) * -1 AS DIFF
      
from agg_join
Group by grade
Order by DIFF DESC
Limit 1
Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
Gwillis
  • 3
  • 2
  • 1
    Seems to me you can run these yourself to see if they work. Are you asking us to check your homework before you submit it? This is a *question and answer* site. Note that *question* is singular, not plural. You've asked two questions, both of which it appears you've already answered yourself. After you've executed the two solutions yourself, you'll know if they work or not, and your instructor will decide if it's the answer they're looking for or not. – Ken White Oct 14 '21 at 02:26
  • @KenWhite Thanks for the response. This isnt for HW but practicing hypothetical questions for an interview. If you could point me in the direction of where I can run this code i dont really know where i could do that ( I have been using websites to learn how to use SQL). So i couldve used a few pointers. I appreciate your help and preemptive inferences. – Gwillis Oct 14 '21 at 02:45
  • You can use a fiddle site like https://dbfiddle.uk/?rdbms=postgres_13 for testing. – Erwin Brandstetter Oct 14 '21 at 02:48
  • I can't tell you where you can run the SQL, because you've not identified the DBMS you're using. (It's clearly not both MySQL and PostgreSQL, because if you're asking this question here you're not experienced enough to be working with both simultaneously.) We also don't recommend off-site services here, so telling you where you can find such a site would be against site guidelines. You'll find your experiences here will be much better if you spend some time taking the [tour] and reading the [help] pages to learn how the site works before you begin posting. – Ken White Oct 14 '21 at 02:49
  • And as far as my *presumptive inferences*, if it walks like a duck, and swims like a duck, and quacks like a duck... We see literally thousands of homework questions a month around this time of year, and based on exactly what you posted, there's no reason to think this isn't another one of them. – Ken White Oct 14 '21 at 02:51
  • @KenWhite Atleast this time you gave me a few actual helpful pointers. I appreciate the help. Starting new things is hard... and to add a note I thought this was PostgreSQL sooo yeah. Take with that what you will. Big shoutout to Erwin for helping out! – Gwillis Oct 14 '21 at 02:55

1 Answers1

0

What percent of students attend school on their birthday?

SELECT 100.0
     * count(*) FILTER (WHERE EXISTS (SELECT FROM attendance_events a
                                      WHERE a.student_id = s.student_id
                                      AND f_mmdd(a.date) = f_mmdd(s.date_of_birth)
                                     ))
     / count(*) AS percentage
FROM   all_students s;

Where the custom function f_mmdd() is defined here:

See:

About the aggregate FILTER clause:

Which grade level had the largest drop in attendance between yesterday and today?

SELECT s.grade_level
     , count(*) FILTER (WHERE a.date = CURRENT_DATE - 1) AS yesterday_attendance
     , count(*) FILTER (WHERE a.date = CURRENT_DATE) AS today_attendance
FROM   attendance_events a
JOIN   all_students s USING (student_id)
WHERE  a.date IN (CURRENT_DATE, CURRENT_DATE -1)  -- logically redundant
GROUP  BY s.grade_level
ORDER  BY today_attendance - yesterday_attendance
    -- short for: yesterday_attendance - today_attendance DESC
LIMIT  1;

WHERE a.date IN (CURRENT_DATE, CURRENT_DATE -1) is logically redundant, but makes the query much faster.

Read up and try to understand why these work, if you are not solving them yourself.

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228