Data Modeling in Oracle or Snowflake | Dataford Interview Questions

Explain your experience with data modeling and performance optimization in Oracle or Snowflake.

For the SQL portion, write a PostgreSQL query that summarizes posted transaction activity during the first quarter of 2025 for every active account. The query should preserve active accounts without qualifying transactions and resolve missing account type information clearly.

Output

One row per active account, with account_id, account_name, account_type, total_posted_amount, and posted_transaction_count.
Include only transactions posted from January 1 through March 31, 2025.
Sort by total_posted_amount descending, then account_id ascending.

Schema

account_types

Column	Type	Description
account_type_idPK	INT	Unique account type identifier
account_type	VARCHAR(40)	Account type label

accounts

Column	Type	Description
account_idPK	INT	Unique account identifier
account_name	VARCHAR(100)	Account holder or account name
account_type_id	INT	References account_types.account_type_id
is_active	BOOLEAN	Whether the account is currently active

transactions

Column	Type	Description
transaction_idPK	INT	Unique transaction identifier
account_id	INT	References accounts.account_id
posted_at	TIMESTAMP	Transaction posting timestamp
amount	NUMERIC(12,2)	Signed transaction amount
status	VARCHAR(20)	Transaction processing status

Your question is Data Modeling in Oracle or Snowflake. Start with the requirements and the three tables in the Question tab.

Run and submit as often as you like. When you're ready, talk me through your approach or go straight to the code.

Problem