Differentiate pandas objects and SQL filters

Python (pandas)

1. What is the difference between a pandas Series and a pandas DataFrame?
   • Discuss structure (1D vs 2D), indexing, column labels, and common use cases.

SQL

2. What is the difference between WHERE and HAVING?

   • When do you use each in queries that include GROUP BY and aggregates?

3. Write SQL to find duplicates.

Table

Assume a table of users:

• users (
  • user_id BIGINT PRIMARY KEY,
  • email VARCHAR,
  • created_at TIMESTAMP )

Tasks

A. Return emails that appear more than once, with their duplicate count.

• Output columns: email, dup_count

B. Return the full rows for users whose email is duplicated.

• Output columns: user_id, email, created_at
• If there are multiple users with the same email, return all of them.

Assumptions

• Treat NULL emails as non-duplicates unless specified otherwise.
• SQL dialect can be ANSI SQL (window functions allowed).

Differentiate pandas objects and SQL filters

Table users

column	type	constraints
user_id	BIGINT	PRIMARY KEY
email	VARCHAR
created_at	TIMESTAMP

Sample data:

created_at	email	user_id
2023-01-01 09:00:00	alice@example.com	1
2023-01-02 10:00:00	alice@example.com	2
2023-01-03 11:00:00	bob@example.com	3
2023-01-04 12:00:00	bob@example.com	4
2023-01-05 13:00:00	bob@example.com	5
2023-01-06 14:00:00	carol@example.com	6
2023-01-07 15:00:00	NULL	7
2023-01-08 16:00:00	dave@example.com	8

Expected output

Task A: List each non-NULL email that appears more than once and its duplicate count. Results are ordered by email for determinism.

dup_count	email
2	alice@example.com
3	bob@example.com

Hints

1. Use WHERE to exclude NULL emails before grouping so they are treated as non-duplicates.
2. GROUP BY email and use HAVING COUNT(*) > 1 to keep only duplicated emails.
3. WHERE filters rows before grouping; HAVING filters groups after aggregation.
4. ORDER BY email to produce deterministic output.
5. For full duplicate rows (Task B), join users to the duplicated email set or use a window function (COUNT(*) OVER (PARTITION BY email)) and filter where the window count > 1.