Explain Pandas and SQL Basics

You are interviewing for a Data Engineer internship. Answer the following short data-manipulation questions:

1. In pandas, what is the difference between a Series and a DataFrame? Compare their dimensionality, indexing behavior, and common use cases.

2. In SQL, what is the difference between WHERE and HAVING? Explain when each filter is applied and whether aggregate expressions can be used.

3. Consider a table transactions_raw with the following schema:

   • ingest_id BIGINT
   • account_id BIGINT
   • transaction_id BIGINT
   • amount DECIMAL(12,2)
   • transaction_ts TIMESTAMP

   Assume transaction_ts is stored in UTC, and there are no foreign-key relationships relevant to this task. Define a duplicate as multiple rows with the same (account_id, transaction_id, amount, transaction_ts). Write a SQL query that returns all duplicate groups with the output columns: account_id, transaction_id, amount, transaction_ts, duplicate_count.

Explain Pandas and SQL Basics

Write an SQL query that, for every user id, returns the first date they were recorded as active and the last date they were recorded as inactive.

Table activity

column	type	constraints
id	INTEGER
date	DATE
active_status	VARCHAR(20)

Sample data:

active_status	date	id
active	2023-01-01	1
inactive	2023-01-10	1
active	2023-02-01	1
inactive	2023-01-05	2
active	2023-03-20	2

Expected output

For each user ID, show first active date and last inactive date

first_active_date	id	last_inactive_date
2023-01-01	1	2023-01-10
2023-03-20	2	2023-01-05

Hints

1. Use conditional aggregation (MIN/MAX with CASE) or window functions