The SQL equivalent of pandas sort, group by, count and first

Question

I have a table that looks like:

I need to determine what the top 3 most common viewplanes are captured when first scanning a new patient (I believe the patients are indicated by the subject_label column).

In Pandas, this looks like:

df.sort_values('datetime').groupby('subject_label').first().viewplane

In SQL, I have tried:

  WITH added_row_number
  (SELECT
    *,
    ROW_NUMBER() OVER(PARTITION BY subject_label  ORDER BY datetime ASC) AS row_number
  FROM image_list_csv)

SELECT lower(viewplane),
       COUNT(lower(viewplane)) OVER (ORDER BY datetime ASC) AS running_total
  FROM added_row_number
  WHERE ROW_NUMBER = 1
  ORDER BY running_total DESC;

Which gives:

I have also tried:

WITH added_row_number AS ( SELECT *, ROW_NUMBER() OVER(PARTITION BY subject_label, datetime ORDER BY datetime DESC) AS row_number FROM image_list_csv ) SELECT
LOWER(viewplane), datetime FROM added_row_number WHERE row_number = 1;

Which gives: