Database design for tracking student exam performance at topic level with possibly 10 billion rows

I am developing a test/exam solution where We have around 100K questions (MCQ / Objective Type). Each question belongs to a topic and each topic belongs to a subject. Students practice these questions as part of weekly/monthly tests, but students can attempt questions just as practice workout and without any test. ## Tables **subject** - id - name **Topic** - id - name - subject_id **Question** - id - text - options columns - correct_option - difficulty **topic_questions** - topic_id - question_id **question_attempts** - user_id - question_id - was_correct //if user got this question right - attempt_date ## Data size - Need to support around 100K questions - could have 100K users - question_attempt could go as big as 100K*100K or more. Users subscribe and use the app over the period of 2 to 3 years during which period they take tests, practice questions as workouts etc. All of the students are competing for a single exam. ## Use cases I need to support - Calculate question difficulty score based on success/failed attempts - Get list of unattempted questions for users to practice. - Each time user click next get an unattempted question for given topic, or subject. - Calculate score for user's strength/weakness for subject and topics based on user's question attempt success/fail - Get list of questions which user attempted last week but failed and let user practice them - Determine user's overall rank among other users based on his attempts Major problem I see is : Finding unattempted questions, As it would need join between question and question_attempt which is very huge and querying last weeks failed question for user For calculating topic/subject performance, i could do jobs which runs at midnight and could get away with lower performance. Currently we use Mysql