Active Flags in ER Joins

MediumSQL · PostgreSQL

Problem

Given an ER diagram of restaurants, orders, order_items and menus, write SQL to generate several result sets, applying each table's is_active flag in every join condition.

Asked in the technical phone screen stage. No window functions needed; the catch was the is_active filter on every join.

Output

Return labeled rows for active restaurant menus, active orders, and active order items.
Columns: result_set, restaurant_id, restaurant_name, related_id, item_name, and amount.
Sort by result_set, restaurant_id, and related_id.

Schema

restaurants

Column	Type	Description
restaurant_idPK	INT	Unique restaurant identifier
restaurant_name	VARCHAR(100)	Restaurant display name
is_active	BOOLEAN	Whether the restaurant is currently active

orders

Column	Type	Description
order_idPK	INT	Unique order identifier
restaurant_id	INT	Referenced restaurant
customer_id	VARCHAR(20)	Customer identifier
ordered_at	TIMESTAMP	Order creation timestamp
is_active	BOOLEAN	Whether the order is active

order_items

Column	Type	Description
order_item_idPK	INT	Unique order item identifier
order_id	INT	Referenced order
menu_id	INT	Referenced menu item
quantity	INT	Quantity ordered
is_active	BOOLEAN	Whether the order item is active

menus

Column	Type	Description
menu_idPK	INT	Unique menu item identifier
restaurant_id	INT	Referenced restaurant
item_name	VARCHAR(100)	Menu item name
price	NUMERIC(10,2)	Current menu item price
is_active	BOOLEAN	Whether the menu item is active

Tablesrestaurantsordersorder_itemsmenus