Barclays & HSBC: Data Scientist

SELECT name, salary,
       ROW_NUMBER() OVER (ORDER BY salary DESC) AS rn,     -- 1,2,3,4 (unique)
       RANK()       OVER (ORDER BY salary DESC) AS rk,     -- 1,2,2,4 (gaps after ties)
       DENSE_RANK() OVER (ORDER BY salary DESC) AS drk     -- 1,2,2,3 (no gaps)
FROM employees;

• Add PARTITION BY dept for per-group rankings, e.g. the top 3 salaries per department.

• UNION combines results and removes duplicates, which needs a sort or hash, so it is slower.
• UNION ALL keeps all rows, including duplicates, and is faster.
• Both require the same number of columns with compatible types. Use UNION ALL unless you truly need deduplication.

import pandas as pd, matplotlib.pyplot as plt
orders = pd.read_csv("orders.csv"); customers = pd.read_csv("customers.csv")
df = orders.merge(customers, on="customer_id", how="left", validate="many_to_one")
monthly = (df.assign(month=pd.to_datetime(df.order_date).dt.to_period("M").astype(str))
             .groupby(["month", "segment"]).amount.sum().unstack(fill_value=0))
monthly.plot(kind="line", marker="o", figsize=(8, 4), title="Revenue by segment"); plt.tight_layout(); plt.show()

• Mention how= options, validate= to catch duplicate keys, and checking for unmatched rows (indicator=True).

from collections import Counter
s = "Madam, I'm Adam"
clean = "".join(ch.lower() for ch in s if ch.isalnum())
print(Counter(clean))                 # character frequencies
print(clean == clean[::-1])           # palindrome check -> True
def reverse_sub(s, i, j): return s[:i] + s[i:j+1][::-1] + s[j+1:]   # reverse s[i..j]

• MCP (Model Context Protocol): a standard for exposing tools, resources and prompts to LLM apps through servers, so any compatible agent can discover and call them.
• In a bank:
• Wrap internal systems (customer lookup, transaction history, policy search) as MCP servers with strict authentication, scopes and audit.
• Agents compose them for tasks such as complaint triage or KYC refresh.
• Controls:
• Read-only by default.
• Human approval for any action that changes money or records.
• PII minimisation.
• Logging of every tool call.
• Treat tool outputs as untrusted (prompt-injection risk).

• Clarify impact and deadlines with stakeholders. Use an impact × urgency matrix.
• Communicate trade-offs early: "I can deliver A today and B tomorrow. Is that acceptable?"
• Unblock others first, time-box investigations, and automate repeated work.
• Example: "During quarter-end, I triaged three data requests by regulatory deadline, delivered the regulator's one first, and agreed new dates for the others. All three landed on time."