EF vs Raw SQL | Dataford Interview Questions

What are the advantages of using Entity Framework compared to raw SQL queries?

Use the supplied PostgreSQL schema to write the raw SQL equivalent of a common Entity Framework data query. Return all active users with their completed order count and completed order revenue.

Output

One row per active user, including users with no completed orders
Columns: user_id, display_name, completed_order_count, completed_revenue
Sort by completed_revenue descending, then display_name ascending

Schema

users

Column	Type	Description
idPK	INT	Unique user identifier
display_name	VARCHAR(100)	User display name
email	VARCHAR(255)	User email address
status	VARCHAR(20)	User account status

orders

Column	Type	Description
idPK	INT	Unique order identifier
user_id	INT	User who placed the order
status	VARCHAR(20)	Order lifecycle status
created_at	DATE	Order creation date

order_items

Column	Type	Description
idPK	INT	Unique order item identifier
order_id	INT	Order containing the item
quantity	INT	Quantity purchased
unit_price	NUMERIC(10,2)	Price per item

Your question is EF vs Raw SQL. 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