Find Students with Study Spiral Pattern Python solution?

In Python, load the records into a Pandas DataFrame, sort by student and time, use groupby on student_id with shift to create lagged columns, build a boolean mask for the spiral condition, and return the unique matching student IDs.

Is Find Students with Study Spiral Pattern easy or hard?

It is rated Hard on FleetCode with a 23.1% acceptance rate. The difficulty comes from correctly ordering the records within each student and encoding the spiral condition across consecutive rows, not from the amount of code.

How to solve Find Students with Study Spiral Pattern in O(n log n)?

Partition the table by student_id, order each partition by the time column, and apply LAG or LEAD to compare each row with its previous or next row. Filter rows that match the spiral condition and return the distinct student IDs.

What is the best approach for Find Students with Study Spiral Pattern?

The best approach is to use window functions (LAG or LEAD) partitioned by student and ordered by time in SQL, or the equivalent groupby with shift in Pandas. This checks each record against its neighbor in a single pass per student without a self-join.

What data structure is used in Find Students with Study Spiral Pattern?

This is a database problem, so the main structure is the table itself, processed with window functions or DataFrame operations. No custom data structure like a hash map or heap is required.

What is the time complexity of Find Students with Study Spiral Pattern?

The optimal solution runs in O(n log n) time because the records must be sorted by time within each student partition. The space complexity is O(n) for the intermediate results.

Is Find Students with Study Spiral Pattern asked at Google, Amazon, or Meta?

The platform does not list company tags for this problem, so there is no verified data on which companies ask it. Check recent interview reports on company-specific forums before you plan around it.

Description Solution Video 22:03

3617. Find Students with Study Spiral Pattern

Hard23.1% acceptance

Table: students

+--------------+---------+
| Column Name  | Type    |
+--------------+---------+
| student_id   | int     |
| student_name | varchar |
| major        | varchar |
+--------------+---------+
student_id is the unique identifier for this table.
Each row contains information about a student and their academic major.

Table: study_sessions

+---------------+---------+
| Column Name   | Type    |
+---------------+---------+
| session_id    | int     |
| student_id    | int     |
| subject       | varchar |
| session_date  | date    |
| hours_studied | decimal |
+---------------+---------+
session_id is the unique identifier for this table.
Each row represents a study session by a student for a specific subject.

Write a solution to find students who follow the Study Spiral Pattern - students who consistently study multiple subjects in a rotating cycle.

A Study Spiral Pattern means a student studies at least 3 different subjects in a repeating sequence
The pattern must repeat for at least 2 complete cycles (minimum 6 study sessions)
Sessions must be consecutive dates with no gaps longer than 2 days between sessions
Calculate the cycle length (number of different subjects in the pattern)
Calculate the total study hours across all sessions in the pattern
Only include students with cycle length of at least 3 subjects

Return the result table ordered by cycle length in descending order, then by total study hours in descending order.

The result format is in the following example.

Example:

Input:

students table:

+------------+--------------+------------------+
| student_id | student_name | major            |
+------------+--------------+------------------+
| 1          | Alice Chen   | Computer Science |
| 2          | Bob Johnson  | Mathematics      |
| 3          | Carol Davis  | Physics          |
| 4          | David Wilson | Chemistry        |
| 5          | Emma Brown   | Biology          |
+------------+--------------+------------------+

study_sessions table:

+------------+------------+------------+--------------+---------------+
| session_id | student_id | subject    | session_date | hours_studied |
+------------+------------+------------+--------------+---------------+
| 1          | 1          | Math       | 2023-10-01   | 2.5           |
| 2          | 1          | Physics    | 2023-10-02   | 3.0           |
| 3          | 1          | Chemistry  | 2023-10-03   | 2.0           |
| 4          | 1          | Math       | 2023-10-04   | 2.5           |
| 5          | 1          | Physics    | 2023-10-05   | 3.0           |
| 6          | 1          | Chemistry  | 2023-10-06   | 2.0           |
| 7          | 2          | Algebra    | 2023-10-01   | 4.0           |
| 8          | 2          | Calculus   | 2023-10-02   | 3.5           |
| 9          | 2          | Statistics | 2023-10-03   | 2.5           |
| 10         | 2          | Geometry   | 2023-10-04   | 3.0           |
| 11         | 2          | Algebra    | 2023-10-05   | 4.0           |
| 12         | 2          | Calculus   | 2023-10-06   | 3.5           |
| 13         | 2          | Statistics | 2023-10-07   | 2.5           |
| 14         | 2          | Geometry   | 2023-10-08   | 3.0           |
| 15         | 3          | Biology    | 2023-10-01   | 2.0           |
| 16         | 3          | Chemistry  | 2023-10-02   | 2.5           |
| 17         | 3          | Biology    | 2023-10-03   | 2.0           |
| 18         | 3          | Chemistry  | 2023-10-04   | 2.5           |
| 19         | 4          | Organic    | 2023-10-01   | 3.0           |
| 20         | 4          | Physical   | 2023-10-05   | 2.5           |
+------------+------------+------------+--------------+---------------+

Output:

+------------+--------------+------------------+--------------+-------------------+
| student_id | student_name | major            | cycle_length | total_study_hours |
+------------+--------------+------------------+--------------+-------------------+
| 2          | Bob Johnson  | Mathematics      | 4            | 26.0              |
| 1          | Alice Chen   | Computer Science | 3            | 15.0              |
+------------+--------------+------------------+--------------+-------------------+

Explanation:

Alice Chen (student_id = 1):
- Study sequence: Math → Physics → Chemistry → Math → Physics → Chemistry
- Pattern: 3 subjects (Math, Physics, Chemistry) repeating for 2 complete cycles
- Consecutive dates: Oct 1-6 with no gaps > 2 days
- Cycle length: 3 subjects
- Total hours: 2.5 + 3.0 + 2.0 + 2.5 + 3.0 + 2.0 = 15.0 hours
Bob Johnson (student_id = 2):
- Study sequence: Algebra → Calculus → Statistics → Geometry → Algebra → Calculus → Statistics → Geometry
- Pattern: 4 subjects (Algebra, Calculus, Statistics, Geometry) repeating for 2 complete cycles
- Consecutive dates: Oct 1-8 with no gaps > 2 days
- Cycle length: 4 subjects
- Total hours: 4.0 + 3.5 + 2.5 + 3.0 + 4.0 + 3.5 + 2.5 + 3.0 = 26.0 hours
Students not included:
- Carol Davis (student_id = 3): Only 2 subjects (Biology, Chemistry) - doesn't meet minimum 3 subjects requirement
- David Wilson (student_id = 4): Only 2 study sessions with a 4-day gap - doesn't meet consecutive dates requirement
- Emma Brown (student_id = 5): No study sessions recorded

The result table is ordered by cycle_length in descending order, then by total_study_hours in descending order.

22:03

Still stuck?Read the written solution, or watch a walkthrough.

Open solution

LanguageLoading editor…

1# Write your MySQL query statement below

Starter codeMySQLSpaces: 4

Examples

Case 1

Input

{"headers":{"students":["student_id","student_name","major"],"study_sessions":["session_id","student_id","subject","session_date","hours_studied"]},"rows":{"students":[[1,"Alice Chen","Computer Science"],[2,"Bob Johnson","Mathematics"],[3,"Carol Davis","Physics"],[4,"David Wilson","Chemistry"],[5,"Emma Brown","Biology"]],"study_sessions":[[1,1,"Math","2023-10-01",2.5],[2,1,"Physics","2023-10-02",3.0],[3,1,"Chemistry","2023-10-03",2.0],[4,1,"Math","2023-10-04",2.5],[5,1,"Physics","2023-10-05",3.0],[6,1,"Chemistry","2023-10-06",2.0],[7,2,"Algebra","2023-10-01",4.0],[8,2,"Calculus","2023-10-02",3.5],[9,2,"Statistics","2023-10-03",2.5],[10,2,"Geometry","2023-10-04",3.0],[11,2,"Algebra","2023-10-05",4.0],[12,2,"Calculus","2023-10-06",3.5],[13,2,"Statistics","2023-10-07",2.5],[14,2,"Geometry","2023-10-08",3.0],[15,3,"Biology","2023-10-01",2.0],[16,3,"Chemistry","2023-10-02",2.5],[17,3,"Biology","2023-10-03",2.0],[18,3,"Chemistry","2023-10-04",2.5],[19,4,"Organic","2023-10-01",3.0],[20,4,"Physical","2023-10-05",2.5]]}}

Your code is saved on this device.Solution