Wells Fargo & Deutsche Bank: Data / Analytics

• ROWID: a physical address pseudocolumn identifying where a row is stored (data object, file, block, slot). It is fast for direct access, unique per row in a table, but can change after operations such as table moves or export/import. Not for business logic.
• ROW_NUMBER(): an analytic function that assigns a sequential number to rows in a result set based on OVER (PARTITION BY ... ORDER BY ...). Used for ranking, top-N per group and deduplication.
• Also: ROWNUM is a pseudocolumn assigned before ORDER BY, a classic pitfall for top-N queries.

SELECT DISTINCT salary
FROM (SELECT salary, DENSE_RANK() OVER (ORDER BY salary DESC) AS rk FROM employees) t
WHERE rk = 3;
-- alternative: SELECT salary FROM employees ORDER BY salary DESC OFFSET 2 ROWS FETCH NEXT 1 ROW ONLY;  (ties differ!)

• Explain the difference with ties: DENSE_RANK gives the third distinct salary; OFFSET gives the third row.

WITH tx AS (
  SELECT customer_id, txn_date,
         CASE WHEN type = 'CREDIT' THEN amount ELSE -amount END AS signed_amt
  FROM transactions
)
SELECT customer_id, txn_date, signed_amt,
       SUM(signed_amt) OVER (PARTITION BY customer_id ORDER BY txn_date
                             ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS running_balance
FROM tx;

• Read the execution plan: full scans, bad join orders, sorts spilling to disk.
• Indexes on the join and filter columns (composite, in the right column order); avoid functions on indexed columns in WHERE.
• Reduce data early: filter before joining, select only the needed columns, pre-aggregate.
• Partition large fact tables by date; use materialised views for repeated reports; update statistics.
• Measure before and after, and keep correctness checks (row counts, totals).

import heapq
def dijkstra(graph, src):                 # graph: {u: [(v, w), ...]}
    dist = {src: 0}; pq = [(0, src)]
    while pq:
        d, u = heapq.heappop(pq)
        if d > dist.get(u, float("inf")): continue
        for v, w in graph.get(u, []):
            nd = d + w
            if nd < dist.get(v, float("inf")):
                dist[v] = nd; heapq.heappush(pq, (nd, v))
    return dist

• O((V + E) log V) with a binary heap; it needs non-negative weights (otherwise use Bellman-Ford).

Answer (STAR):

• "Monthly regulatory reports took 3 days of manual Excel work. I built a Python + SQL pipeline (pandas, templated outputs, validation checks) scheduled in Airflow."
• "The time dropped to 2 hours with automated reconciliation against source totals. Errors went from about 4 a month to 0, and the team re-used the framework for 5 other reports."
• Banks value control, accuracy and auditability, so mention the validation checks and documentation.