Obviously in your case some trivial SQL would have sufficed, which I feel like should have been caught in an interview but could be excused for a junior eng.
Also Pandas is not an amazing choice for data engineering tasks in general IMO, type conversion wonkiness is one reason I have questions about people who decide to use Pandas for legitimate data engineering tasks (as opposed to exploratory work or analysis).
Using pandas but your data doesn’t fit in memory anymore? Here just use this fragile series of packages to distribute it across processors. Don’t reevaluate whether Pandas/Python is still the best choice, just layer more things in.
Run into issues with that approach? Here lift and shift your whole thing into Spark! Don’t ever optimise or actually engineer your process, just layer more things in!!!
Once you have done that, the logic will be easily translatable to SQL, and your brain is prepared for the SQL/relational-algebra way of thinking.
And good luck! You are doing it right!