Full Stack Interview Cheatsheet

SQL clause order

SELECT
FROM
JOIN ... ON ...
WHERE
GROUP BY
HAVING
ORDER BY
LIMIT

SQL quick reference

-- aggregates
COUNT(*), SUM(col), AVG(col), MAX(col), MIN(col)

-- filter after GROUP BY
HAVING COUNT(*) > 1

-- null checks
WHERE col IS NULL
WHERE col IS NOT NULL

-- conditional aggregate
SUM(CASE WHEN condition THEN amount ELSE 0 END) AS bucket

-- exclude rows based on subquery
WHERE id NOT IN (SELECT equipment_id FROM rentals WHERE ...)

-- date math (Postgres)
CURRENT_DATE
CURRENT_DATE - INTERVAL '90 days'
end_date - start_date  -- gives number of days

-- params (never string format SQL)
cur.execute("SELECT * FROM t WHERE id = %s", (id,))

-- CTE
WITH name AS (
    SELECT ...
)
SELECT * FROM name

Flask route patterns

# GET list
@app.route("/items", methods=["GET"])
def list_items():
    status = request.args.get("status")  # query param

# GET single by id (path param)
@app.route("/items/<int:item_id>", methods=["GET"])
def get_item(item_id):  # passed automatically
    pass

# POST create
@app.route("/items", methods=["POST"])
def create_item():
    data = request.get_json()

When to use each:

Path param /items/42 → identifying one specific resource
Query param /items?status=active → filtering a list
Body → creating or updating data (POST/PUT only)

Schema design rules

Always include:

id SERIAL PRIMARY KEY on every table
Foreign keys with REFERENCES table(id)
NOT NULL on required fields
TEXT for strings, NUMERIC for money, DATE for dates

Naming:

Tables: plural, lowercase (customers, rentals)
Foreign keys: table_id (customer_id, equipment_id)

When to split tables:

Different data belongs in different tables
Avoid storing derived data — calculate in queries
Many-to-many → junction table

Common tradeoff:

One table with a type column vs separate tables → use one table when the shape is the same, separate when fields differ significantly

React patterns

// state
const [items, setItems] = useState<Item[]>([])

// fetch on mount
useEffect(() => {
  fetch(`${API}/items`)
    .then(r => r.json())
    .then(setItems)
}, [])

// form state (one handler for all inputs)
const [form, setForm] = useState({ col1: '', col2: '' })

function handleChange(e: React.ChangeEvent<HTMLInputElement>) {
  setForm({ ...form, [e.target.name]: e.target.value })
}

// POST submit
function handleSubmit(e: React.FormEvent) {
  e.preventDefault()
  fetch(`${API}/items`, {
    method: 'POST',
    headers: { 'Content-Type': 'application/json' },
    body: JSON.stringify(form),
  })
  .then(r => r.json())
  .then(newItem => setItems([...items, newItem]))
}

// GET with query params
const params = new URLSearchParams({ start, end })
fetch(`${API}/items?${params}`)

curl

# GET
curl http://localhost:5001/items

# GET with query params (always quote the URL)
curl "http://localhost:5001/items?status=active&type=lift"

# POST
curl -X POST http://localhost:5001/items -H "Content-Type: application/json" -d '{"col1": "val1"}'

# GET path param
curl http://localhost:5001/items/42

Datetime (Python)

from datetime import date, datetime, timedelta

date.today()
datetime.strptime("2026-05-01", "%Y-%m-%d")  # string → date
date(2026, 5, 1).strftime("%Y-%m-%d")        # date → string
date.today() - timedelta(days=90)            # 90 days ago