Coding questions · Rbcroyalbank
Explain pandas and SQL basics
This question evaluates familiarity with pandas data structures (Series vs DataFrame), SQL filtering semantics (WHERE vs HAVING), and practical SQL aggregation for detecting duplicate records, assessing competencies in data manipulation, aggregation, and basic data quality checks.
You are interviewing for a Data Engineer co-op/intern role. Answer the following short technical questions.
Python / pandas:
- What is the difference between a pandas
Seriesand a pandasDataFrame? Give one practical example of when you would use each.
SQL concepts:
2. What is the difference between WHERE and HAVING in SQL, and when should each be used?
SQL query task: 3. You have a table:
customer_events
event_idBIGINTcustomer_emailSTRINGsource_systemSTRINGcreated_atTIMESTAMP
Assume created_at is stored in UTC. Write a SQL query to find duplicate customer_email values in the table. A duplicate means the same customer_email appears more than once in the full table. Return these output columns:
customer_emailduplicate_count
Only include emails that appear more than once.
Explain pandas and SQL basics
Table customer_events
| column | type | constraints |
|---|---|---|
| event_id | BIGINT | |
| customer_email | VARCHAR(254) | |
| source_system | VARCHAR(50) | |
| created_at | TIMESTAMP |
Sample data:
| created_at | customer_email | event_id | source_system |
|---|---|---|---|
| 2023-07-01 10:15:00 | alice@example.com | 1 | web |
| 2023-07-01 10:16:00 | bob@example.com | 2 | mobile |
| 2023-07-02 11:30:00 | alice@example.com | 3 | mobile |
| 2023-07-03 12:00:00 | charlie@example.com | 4 | web |
| 2023-07-03 12:10:00 | diana@example.com | 5 | partner |
| 2023-07-04 13:00:00 | bob@example.com | 6 | web |
| 2023-07-05 09:25:00 | bob@example.com | 7 | partner |
| 2023-07-06 15:45:00 | eve@example.com | 8 | web |
| 2023-07-06 16:00:00 | frank@example.com | 9 | mobile |
| 2023-07-07 08:10:00 | alice@example.com | 10 | partner |
| 2023-07-07 10:05:00 | diana@example.com | 11 | web |
Expected output
All customer_email values that occur more than once, with the number of occurrences for each email.
| customer_email | duplicate_count |
|---|---|
| alice@example.com | 3 |
| bob@example.com | 3 |
| diana@example.com | 2 |
Hints
- Aggregate by customer_email and count rows per email.
- Use HAVING to filter aggregated results to counts greater than 1.
- Add ORDER BY for deterministic output matching the sample.