Upgrad | Backend | SD2 | Bangalore

Round 1

Questions: You are tasked with designing a scalable Learning Management System (LMS).

All of the models will inherit createdAt and updatedAt.

Users:

• id
• name
• email
• pass_hash
• is_deleted

Roles:

• id (PK)
• name UNIQUE ('Student', 'Faculty', 'Admin')

User_roles:

• role_id
• user_id
• view_access
• create_access

Permission:

• permission_id (PK)
• permission_name UNIQUE ('VIEW_CONTENT', 'CREATE_CONTENT')

I asked the interviewer if a single user could have multiple roles. I suggested using a toggle button to switch roles, but he said that users would have only one role, which was fine. He said he is more interested in batches, programs and content schema.

Programs:

• id (PK)
• name
• description
• type ENUM(grad, post-grad, PhD, etc.) - can be a static table as well
• concentrations ENUM(btech, barch….)

Semesters:

• id (PK)
• program_id (FK)
• name
• duration
• total_credits
• start_date
• end_date

Batches:

• id (PK)
• semester_id (FK)
• start_date
• end_date

Enrollments:

• id
• user_id (FK)
• batch_id (FK)

After this, the interviewer asked if there could be multiple batches of the same semester. I explained that this could work perfectly, as batches can have entries for the same semester. I also mentioned that the tables for programs, semesters, and batches are information tables rather than interaction tables. He seemed hesitant and said we could discuss this for hours, so we moved forward.

Subjects:

• id (PK)
• semester_id (FK)
• name
• description
• durations

Chapters:

• id (PK)
• subject_id (FK)
• name
• description
• credit
• durations

Even after I created the content, he asked how we would handle multiple media types, and I said that chapters could have multiple content entries.

Contents:

• id
• chapter_id
• format_type ENUM(video, text, audio, PDF...)
• level or doc_type ENUM(main, attachment, sideContent, notes...)

Due to time pressure, I forgot to add is_completed in chapters, subjects, and semesters. There was not much discussion on the schema here; the interviewer said he was interested in how we would calculate and keep track of every content, chapter, semester, and program. I quickly added this:

Content_tracking:

• user_id
• content_id
• progress_percent
• is_completed
• (user_id, content_id) PK

We would update the content progress and save the chapter progress, subject progress, and program progress in cache. Every 15 minutes, we could run a batch to persist this in the database. I started discussing cache eviction policies and how the API would handle cache misses. He said he was not interested in cache or running a batch to persist the data, as students could complain that they had completed the chapter, but it was not reflecting instantly in the program completion progress in case of a cache disaster.

So, I started thinking about ways to reduce write calls to the database by writing every time, like for content progress in content_tracking I suggested that we could mark the progress once the video/content is completed. He said that 99% of the time, people would never finish the video entirely, which was quite reasonable. He emphasized that it should be real-time.

Then, I suggested querying the database like this:

• Chapter progress: (completed chapters) + (percentage of in-progress) / total chapters
• Subject progress: (completed chapters) + (percentage of in-progress) / total subjects

He did not respond and looked confused, indicating he did not like this solution. As I was continuously thinking of more ways to tackle this, I did not communicate as I should have. I asked him to clarify his expectations because we couldn't use Redis or batch processing. If he wanted literal real-time updates, we could either store it in the database or calculate it on the fly. He did not say anything and ended the interview.

I felt a sense of rejection and asked him for feedback. At first, he denied it due to policy, but then he said the solution was not too bad; it could work.

In my opinion, the interviewer seemed confused at times. I got the feeling he was looking for a very specific way of doing things.

After the interview, I asked ChatGPT for help, and I was shocked and proud because for batches, enrollments, and even for content progress, my solution was exactly the same as ChatGPT's. I know this isn't something to feel proud of, but the interviewer never mentioned any issues, so I am still in the dark about what went wrong.

If anyone can help me understand what I did wrong and what could be the reasons for this rejection, I would greatly appreciate it.

Candidate's Approach

• Suggested a schema for the LMS including Users, Roles, Programs, Semesters, Batches, Enrollments, Subjects, Chapters, Contents, and Content_tracking.
• Discussed handling multiple media types in chapters.
• Proposed a caching strategy for tracking content progress and suggested reducing write calls to the database.
• Suggested real-time updates for progress tracking.

Interviewer's Feedback

• Initially denied feedback due to policy but later mentioned that the solution was not too bad and could work.
• Seemed confused at times and indicated a preference for a specific approach.

---