Anubhav Gain

Anubhav Gain

Security Software Engineer · XDR/OXDR Architect · Rust & eBPF · 222+ Research Citations

Categories

Tags

/ 2fa a2a aaai ab aboutme access Access access-control Access-Control access-management acl Active Directory active directory Active Response active-directory Active-Directory active-response Active-Response ActiveX actix-web ad-blocking administration admission-control adr advanced-devops Advanced-Logging advanced-threats adversarial-attacks adversarial-defenses adversarial-detection adversarial-examples adversarial-ml adversarial-policy adversarial-transferability Agent-Deployment agent-guardrails Agent-Management agent-poisoning agent-runtime agent-safety agent-scanning agent-security agent-to-agent agentic-ai agile ai AI AI Agents AI Development ai-agents AI-Automation ai-collectives ai-controls ai-cybersecurity ai-detection ai-discovery ai-ethics ai-generated-content ai-integration AI-Marketing ai-models ai-policy ai-powered-scoring AI-Sales ai-security AI-security AI-Security ai-skills ai-threat-detection ai-threat-hunting ai-translation aibom airtable alarms alert-consolidation Alert-Enrichment alert-fatigue alerting Alerts alignment Alpine-Linux amd-sev amplitude amtd analysis analytics Analytics Android anomaly-detection Anomaly-Detection ansible anthropic Anti-Bot Apache api API API Security api-client api-design api-gateway api-management api-security app-control-for-business apparmor AppArmor apple Apple-Intelligence Apple-security applescript application-control application-security applications applocker APS APT-attacks apt-detection arch-linux architecture Architecture architecture-patterns argocd arm-trustzone Artificial-Intelligence asgi assemblyscript astro-ai async Async asynchronous athena ATL Attack-Classification attack-defense attack-detection attack-methodology Attack-Mitigation attack-patterns Attack-Prevention attack-tools attestation audio Audio audit Audit-Logging Auditd auth0 authentication Authentication authentication-security authenticity authorization Authorization auto-reboot automated-monitoring automated-response automated-testing automation Automation autonomous-pentesting autonomous-vehicles autoscaling aws AWS AWS-Bedrock AWS-Lambda awslambda aya Aya azure Azure azure-ad backdoor-attacks backdoor-defense backdoor-detection backend background-services backstage backup Backup bare-metal baseline-analysis baselining bash basics bcc behavioral-analysis behavioral-analytics benchmark benchmarking benchmarks Best Practices best-practices Best-Practices BestPractices bgp Big-Data big-data bind-mounts biometric-security black-box-attacks blacklist blockchain blog-platforms Blogging blue-green blue-team Blue-Team bluechi bochs borrowing bot-management Bot-Management Bot-Protection boto3 bpftrace broadcom browser browser-exploitation brute-force Brute-Force btlo bug-bounty build build-configuration business-intelligence busybox BYOD byova byzantine c C Programming C++ caching caddy calico Campaign-Management canary CAPTCHA career Career CASB catalog cdc cdn CDN centos certificate-authority Certificate-Management certificates certification Cgroups chain-of-thought Chainsaw Change-Management chartmuseum chatbot Chatbot ChatGPT chatops check_wmi_plus choreography ChromaDB chrome ci-cd CI-CD CI/CD cicd cilium circuit-breaker cis CIS cis-benchmark cis-benchmarks cka Claude claude-3-opus claude-code Claude-Haiku cleanup cli CLI-anything cli-tools clickhouse ClickHouse client-go cloud cloud-computing cloud-native Cloud-Native cloud-providers cloud-security Cloud-Security Cloud-Storage cloud-storage cloudflare Cloudflare cloudflared cloudformation cloudfront CloudNative cloudrun cloudwatch cluster cluster-deployment cluster-health cluster-management cluster-setup cmd CMMC cni cnn cocktails code management code-agents code-generation code-hallucination code-integrity code-safety code-signing collaboration columnar com COM Command-Module communication communication-patterns community compensation compliance Compliance compliance-automation compliance-reporting Component-Development Compose compression Compression compute computer-use-agents computer-vision conference-translation confidential-computing configmaps configuration configuration-management consensus constrained-language-mode consul container Container Orchestration Container-Architecture container-management container-monitoring container-orchestration Container-Orchestration container-runtime container-security Container-Security Containerd containerization Containerization containers Containers containment content-attribution content-authenticity content-automation content-filtering content-generation Content-Marketing context-aware contextual-translation continuous-improvement contract-testing controller controller-manager Controllers cookiecutter cooking coredns coreos Coroutines correlation Correlation correlation-rules cors cosmopolitan Cost Optimization cost-optimization Cost-Optimization cpp CPU-Monitoring cqrs crawler crd CRD CRI cri-o critical-analysis CRM cronjob cronjobs cross-account cross-cloud Cross-Cluster-Search Cross-Platform Crypto-Mining CryptoAPI cryptography Cryptography csa csharp css cti custom linux custom-decoders Custom-Rules Customer-Success customization Customization CVE-2025-31200 CVE-2025-31201 cvpr cyber-range-thailand-2024-(qualifier) cyber-threats cyberdefenders cybersec cybersecurity Cybersecurity cygenixctf-write-up d1 d1-database D1-Database daemonsets dashboard dashboards Data-Analysis data-analysis data-analytics data-architecture data-augmentation data-breach data-catalog data-channels data-consistency data-curation data-deduplication data-economics data-fetcher Data-Filtering data-free data-governance Data-Lake data-management data-masking data-migration data-normalization Data-Pipeline data-pipeline data-pipelines data-poisoning data-prepper data-privacy data-processing data-protection Data-Protection data-recovery Data-Residency data-security data-shapley data-valuation data-warehouse database DataBinding dataset-security datasets Datasets datasette daybreak dba ddos-protection DDoS-Protection debian debugging decoders Decoders decoupling deep-learning deepfake deepl-voice deepseek-r1 deepteam defense defense-evasion defensive-security Demo deno dep deployment Deployment deserialization design-patterns desktop-development Desktop-Development detection detection-accuracy detection-engineering Detection-Engineering Developer Tools developer-portal developer-portals development Device Drivers device-guard Device-Monitoring devops DevOps devops-culture devops-journey devsecops DevSecOps devtools devtron DFIR diagrams differential-privacy diffusion-models Digital-Forensics digital-payments Digital-Signatures digital-transformation digitalocean Direct2D Direct3D DirectShow DirectX disaster-recovery discord discovery disk-encryption Disk-Monitoring disk-provisioning distinguished-paper Distributed Systems distributed-security Distributed-Security distributed-systems distributed-transactions distro distrobox diversity django dkim DLP dmarc dnf dns docker Docker document-processing documentation DoD domain domain-administration Domain-Controller domain-driven-design dotnet DPAPI dual-objective duckdb dx-operational-observability DynamicClient dynamodb e-payment ebpf eBPF ec2 ECS ecs edge-ai edge-computing Edge-Computing Edge-Database edge-devices edge-functions edge-security edr EDR edr-bypass efficient-llm eks elastic-alternative Elastic-Stack elasticache elasticsearch Elasticsearch electron elgato elk-stack email Email email-automation Email-Automation Email-Marketing embedded linux embedded-systems Embeddings emnlp encryption Encryption Endpoint-Monitoring endpoint-protection Endpoint-Protection endpoint-security Endpoint-Security Engineering ensemble-methods enterprise Enterprise Enterprise Security enterprise-architecture Enterprise-Architecture enterprise-clustering enterprise-governance enterprise-integration enterprise-security Enterprise-Security environment-variables envoy EPS error-handling etcd ETL etl ETW eureka evaluation evasion Event-Channel event-correlation event-driven event-driven-architecture Event-Logs event-monitoring Event-Monitoring event-sourcing event-streaming Event-Tracing EVTX-Analysis Example exif exploit-mitigation exploit-prevention exploitation falco Fargate fargate fault-tolerance Feature-Engineering feature-flags federated-learning federation fedora fedora-coreos ffmpeg FIDO2 File Integrity file-integrity file-integrity-monitoring File-Integrity-Monitoring file-rule-levels file-transfer filebeat FileVault FIM financial-security Fine-Tuning fingerprinting fintech fips fips-203 Firecracker firefox firehose firewall Firewall Firewall-Monitoring Flow fluentbit Fluentd flux fly forensics Forensics frameworks frontier-models ftp FTP full-stack functions fundamentals future-translation gainsaheb game-theory gans garak Gatekeeper gateway Gateway gcp GCP gcs GDI32 gemini-2.5 genai general generative-ai generative-ai-privacy generative-models gis git github github-actions GitHub-Actions gitlab gitops GitOps glasswing global-delivery Global-Distribution glue gmail go Go golang google google-authenticator google-cloud google-sheets googlecloud governance GPO gpt GPT-4 gpt-4o gpt-trustworthiness gpt3 grafana graph-api graphical interface Graphics API graphql GraphQL group-policy grpc guardrails gui guide hacker-news hackthebox Hadoop hadrian hallucination ham-radio hardening hardware hardware-acceleration hardware-security hashicorp HDFS health-checks health-probes helm helm-charts heroku high-availability High-Availability high-risk-security Hilt hirte Historical-Analysis history holistic-framework homebrew homelab homomorphic-encryption Honeypot hpa hsm htb-labs htb-machines htb-sherlocks htb-sherlocks-write-up-aptnightmare htb-sherlocks-write-up-aptnightmare-2 htb-sherlocks-write-up-bft htb-sherlocks-write-up-bought htb-sherlocks-write-up-brutus htb-sherlocks-write-up-bumblebee htb-sherlocks-write-up-campfire-1 htb-sherlocks-write-up-campfire-2 htb-sherlocks-write-up-compromised htb-sherlocks-write-up-constellation htb-sherlocks-write-up-crewcrow htb-sherlocks-write-up-crownjewel-1 htb-sherlocks-write-up-crownjewel-2 htb-sherlocks-write-up-detroit-becomes-human htb-sherlocks-write-up-heartbreaker-continuum htb-sherlocks-write-up-heartbreaker-denouement htb-sherlocks-write-up-hyperfiletable htb-sherlocks-write-up-jingle-bell htb-sherlocks-write-up-jugglin htb-sherlocks-write-up-knock-knock htb-sherlocks-write-up-litter htb-sherlocks-write-up-lockpick htb-sherlocks-write-up-logjammer htb-sherlocks-write-up-meerkat htb-sherlocks-write-up-neurosync-d htb-sherlocks-write-up-noted htb-sherlocks-write-up-noxious htb-sherlocks-write-up-nubilum-2 htb-sherlocks-write-up-nuts htb-sherlocks-write-up-opsalwarkameez24-1-super-star htb-sherlocks-write-up-opsalwarkameez24-2-magic-show htb-sherlocks-write-up-opsalwarkameez24-4-salsa-dance htb-sherlocks-write-up-optinseltrace24-1-sneaky-cookies htb-sherlocks-write-up-optinseltrace24-3-blizzard-breakdown htb-sherlocks-write-up-optinseltrace24-4-neural-noel htb-sherlocks-write-up-pikaptcha htb-sherlocks-write-up-psittaciformes htb-sherlocks-write-up-reaper htb-sherlocks-write-up-reliablethreat htb-sherlocks-write-up-rogueone htb-sherlocks-write-up-takedown htb-sherlocks-write-up-ticktock htb-sherlocks-write-up-tracer htb-sherlocks-write-up-trent htb-sherlocks-write-up-ultimatum htb-sherlocks-write-up-unit42 htb-sherlocks-write-up-unsupervised htb-write-up-baby htb-write-up-babytwo htb-write-up-breach htb-write-up-data htb-write-up-forgotten htb-write-up-job htb-write-up-lock htb-write-up-manage htb-write-up-media htb-write-up-reset htb-write-up-retro htb-write-up-retrotwo htb-write-up-sendai htb-write-up-slonik htb-write-up-sweep htb-write-up-vulnescape html http HTTP http3 https httpx HubSpot Hugging-Face hugo hybrid-cloud hybrid-quantum-classical Hydra hypothesis-driven I/O-Optimization iac iam iccv icinga iclr icml ics ide identity Identity identity management identity-governance identity-management IDS ieee-sp ignition IIS image-generation imagemagick Implementation in-memory Incident Response incident-response Incident-Response index index-management indexer industrial-iot industrial-security Infopercept infrastructure Infrastructure infrastructure-as-code Infrastructure-as-Code Infrastructure-Monitoring ingress insider-threats installation installutil instruction-tuning instrumentation integration Integration Integration Testing integration-testing intel-sgx inter-agent-security internet interoperability interpretability introduction intrusion-detection intrusion-prevention inventory Invinsense IOCs IOKit ios iOS-development iOS-security iot IPS isa iso isolation Isolation istio IT-security iterators IUnknown jailbreak jamstack jasmin java javascript JavaScript jenkins jest Jetpack Jetpack Compose jinja Journald jq json jsonpath jupyter jwt JWT k8s kafka Kafka kannel kaslr Kata-Containers keepalive Kerberos kernel Kernel Kernel Programming kernel-security Kernel-Tuning Kernel32 key-management keycloak Kibana KIND kinesis kiota kms knowledge-reasoning Kotlin kprobe kpti kubeadm Kubebuilder kubectl kubernetes Kubernetes kubernetes-security kustomize KVM kyber labels lambda LangChain language-processing large-language-models lattice-cryptography launchd LDAP Lead-Generation learning Legacy Code legacy-systems letsdefend letsdefend-challenge letsdefend-write-up-agniane-stealer letsdefend-write-up-astasialoader letsdefend-write-up-batch-downloader letsdefend-write-up-brute-force-attacks letsdefend-write-up-cl0p-malware letsdefend-write-up-compromised-chat-server letsdefend-write-up-compromised-ics-device letsdefend-write-up-compromised-network-printer letsdefend-write-up-confluence-cve-2023-22527 letsdefend-write-up-disclose-the-agent letsdefend-write-up-discord-forensics letsdefend-write-up-dll-stealer letsdefend-write-up-downloader letsdefend-write-up-email-analysis letsdefend-write-up-excel-4.0-macros letsdefend-write-up-golang-ransomware letsdefend-write-up-http-basic-auth letsdefend-write-up-icedid-malware-family letsdefend-write-up-imagestegano letsdefend-write-up-infection-with-cobalt-strike letsdefend-write-up-investigate-web-attack letsdefend-write-up-java-shellcode letsdefend-write-up-kernel-exploit letsdefend-write-up-kimsuky-apt-group letsdefend-write-up-linux-disk-forensics letsdefend-write-up-linux-forensics letsdefend-write-up-linux-memory-forensics letsdefend-write-up-lockbit letsdefend-write-up-log-analysis-with-sysmon letsdefend-write-up-macos-malware letsdefend-write-up-malicious-autoit letsdefend-write-up-malicious-chrome-extension letsdefend-write-up-malicious-doc letsdefend-write-up-malicious-vba letsdefend-write-up-malicious-web-traffic-analys letsdefend-write-up-malicious-wordpress-plugin letsdefend-write-up-memory-analysis letsdefend-write-up-mshtml letsdefend-write-up-ntfs-forensics letsdefend-write-up-obfuscated-javascript letsdefend-write-up-obfuscated-rat letsdefend-write-up-pcap-analysis letsdefend-write-up-pdf-analysis letsdefend-write-up-pdfuri letsdefend-write-up-phishing-email letsdefend-write-up-php-cgi-(cve-2024-4577) letsdefend-write-up-port-scan-activity letsdefend-write-up-powershell-script letsdefend-write-up-presentation-as-a-malware letsdefend-write-up-printnightmare letsdefend-write-up-qakbot-malware letsdefend-write-up-ransomware-attack letsdefend-write-up-registryhive letsdefend-write-up-remote-working letsdefend-write-up-revenge-rat letsdefend-write-up-serpent-stealer letsdefend-write-up-shellshock-attack letsdefend-write-up-suspicious-browser-extension letsdefend-write-up-suspicious-python-package letsdefend-write-up-teamviewer-forensics letsdefend-write-up-upstyle-backdoor letsdefend-write-up-wannacry-ransomware letsdefend-write-up-windows-memory-dump letsdefend-write-up-windows-registry letsdefend-write-up-windows-theme-spoofing letsdefend-write-up-winrar-0-day letsdefend-write-up-yara-rule libvirt lightsail lightweight distro linkding linkerd linux Linux linux development linux from scratch linux kernel linux kernel compilation linux system linux-kernel Linux-Kernel linux-security Linux-Security LiveData liveness lkl Llama-3.1 Llama3 llm LLM llm-activations llm-agents llm-alignment llm-coding llm-distillation llm-guard llm-guardrails llm-influence llm-privacy llm-reliability llm-safety llm-steering llm-translation llms load-balancing load-testing Lockdown-Mode log log-analysis Log-Analysis Log-Analytics Log-Collection log-ingestion log-management Log-Management log-parsing Log-Processing LogcatUDP logging logs Logstash Logwatch lolbas lolbins low-latency lsm LSM lstm lunarvim lxss MAC Machine Learning machine-learning Machine-Learning machine-translation machine-unlearning machinelearning macos macOS macOS-development macOS-security Maintenance malware Malware Malware Analysis malware-analysis malware-detection Malware-Detection malware-protection managed-database management manifest maps Markdown markdown Marketing-Automation master-keys mastodon mcp MCP mdm MDM Media Foundation Media-Storage mediawiki memcached memorization Memory Management Memory Protection memory-management Memory-Management Memory-Monitoring memory-poisoning memory-safety mermaid message-queue messaging metrics metrics-server MFA mfa micro-segmentation micromdm microservices Microservices microsoft Microsoft microsoft-copilot microsoft-graph microsoft-kiota MicroVM microwindows midjourney migration Migration Migrations MikroTik minimalistic os minio misc MISP mitigation MITRE-ATT&CK mitre-attack ml-integration ml-kem mobile-device-management mobile-security Mobile-Security model-behavior model-compression model-context-protocol model-failure model-imitation model-inversion model-poisoning model-repair model-safety model-scanning model-security monitoring Monitoring morphisec msbuild mshta MSI MSSP mtls Multi-Agent Systems multi-cloud Multi-Cloud multi-cluster Multi-Cluster Multi-Service Multi-Site multi-tenancy Multi-Tenancy multi-tenant multi-turn-attacks multilingual-blogs Multimedia multimodal-ai multimodal-safety multipass musl MVVM mysql mythos n00bz-ctf-2024-write-up-(bythew4y-team) n8n nagios Namespaces nano-x Native-Development nats NATS natural-data Navigation ndss nemo-guardrails neovim netdata netflix Network network-access network-correlation Network-Monitoring Network-Performance Network-Scanning network-security Network-Security networking Networking neural-machine-translation neural-networks neurips neuvector nextjs-ai nfs nginx nist nist-standards nlp NLP nlp-security Nmap no-code No-Code node node-affinity node-exporter Node.js nodejs noisy-neighbors nosql notifications Notifications npm NSO-group NTLM oauth oauth2 OAuth2 object-storage Object-Storage objective-c observability observable observable-plot oci-runtime ocr OCSF offensive-security oidc OLE Ole32 Ollama ood-detection ood-generalization ood-robustness open source open-xdr openai OpenAI openapi opensearch OpenSearch openssh openssl OpenSSL opentelemetry openvpn operating system operating-systems Operations operators Operators OperatorSDK optimization Optimization oracle oracle23c orchestration Orchestration organizational-charts OSSEC ot-ics outstanding-paper over-refusal overture-maps owasp ownership OXDR p2p P2P package-management packaging packet-capture packet-processing pact pages pagination partitioning passkeys passwordless patterns pbft PCI-DSS pdf PDF-Reports peer-to-peer Pegasus-protection penetration-testing pentesting performance Performance performance-benchmarking performance-optimization Performance-Optimization permissions persistentvolumeclaims persistentvolumes personalization php physical-attacks pihole pipeline Pipeline-Management pixelmator pixie pkcs11 PKI pki Platform Development platform-engineering playwright pluggy plugin plugins pmp PnP pod-security podman pods poisoning policy-alignment Policy-Monitoring political-bias polyglot-persistence post-quantum-cryptography Postfix postgresql powershell PowerShell presenting pretraining pricing priority-management privacy Privacy privacy-assessment privacy-attacks privacy-controls privacy-engineering privacy-leakage privacy-protection Private-Cloud-Compute Privileged-Access process-exporter Process-Monitoring Process-Supervision processor production Production production-deployment Production-Deployment Production-Setup productivity Productivity programming Programming project-management prometheus prompt-injection promptfoo promptmap2 protocols provable-defense proxy proxy-models pub-sub purification purpleair push-notifications pyodide pypi pytest python Python qemu quadlet quadlets quality-assurance quantum-acceleration quantum-ai quantum-algorithms quantum-computing quantum-nlp quantum-resistance quantum-resistant quarto Query-Language queues quic r2 R2 r2-storage rabbitmq raft rag RAG RAMPART ransomware ransomware-defense rate-limiting Rate-Limiting rbac RBAC rdp rds react readiness readthedocs real-time Real-time Real-time Analytics real-time-analytics real-time-translation real-world-ai red-team red-teaming reddit redis redshift Refactoring reference refusal-direction refusal-vectors Regex regulation regulatory-compliance reinforcement-learning Remote Management remote-access Remote-Commands Remote-Logging ReplicaSet repository management representation-engineering representation-steering resilience resilience4j resource-management Resource-Monitoring resources REST rest-api restore Retrieval-Augmented-Generation retrieval-safety Revenue-Operations risc-v risk-based-alerting risk-management robustness rocky-linux roles rolling-updates Room Rootcheck rootkit rootless rootless-containers route53 Router routing rpki rpm-ostree rsyslog rule-engine rule-options rules Rules runtime-protection runtime-security Runtime-Security RuntimeClass rust Rust s3 s3-compatible S3-Compatible s6-overlay safari safety safety-alignment safety-benchmark safety-classifiers safety-critical safety-reasoning safety-training saga-pattern Sales-Automation Salesforce sandboxed-execution sandboxing SASE sast sbom scalability Scalability scaling scanning sched_ext scheduler scheduling scim screen-sharing Screen-Sharing scripting sdk-development sdk-generation sdlc seamlessm4t search search-engine seccomp Seccomp secplayground-hackloween-ctf-2025-digital-forensics secrets secrets-management secure-boot secure-coding secure-computation secure-element secure-enclave secure-enclaves security Security Security Architecture Security Monitoring Security Platform Security-Analysis security-analytics Security-Analytics security-architecture Security-Architecture Security-Auditing security-automation Security-Automation security-checklist security-commands security-controls security-data-lake security-fallacies Security-Framework Security-Hardening security-implementation Security-Management security-monitoring Security-Monitoring security-operations Security-Operations security-orchestration Security-Orchestration security-patches security-platform security-runtimes security-systems security-taxonomy security-testing security-tools security-trends security-updates selenium self-regulation self-supervised selinux seo SEO seo-optimization server-setup serverless Serverless service mesh service-accounts service-discovery service-mesh service-workers ServiceAccounts Services Shaders sharing shell Shell shell-configuration Shell-Extensions shell-scripting shellcode shellcode-injection shot-scraper Shuffle siem SIEM sigma signaling sigstore SilkETW simultaneous-interpretation single-node sinkhole site-speed situational-awareness skill-security slack slsa smack smallstep smpp SMS sms-gateway smtp SMTP snapshot snort sns snyk soar SOAR SOC soc Social-Media software development software-attestations software-testing sok spark spatialite speech-translation spf sphinx spiffe spire spreadsheet spring-boot spyware-protection sql SQL sql-server sqlite SQLite sqs squarespace sre SRE ssh SSH SSH-security ssl SSL/TLS starship State Management StateFlow static-sites statistical-analysis steganography stepca stig storage Storage-Optimization storageclass streaming Streaming STUN subnets supply-chain supply-chain-security suricata svg swift Swift sysadmin syscollector Syslog syslog sysmon Sysmon System Architecture System Development System Management system services system-administration System-Administration System-APIs System-Audit system-calls system-design system-extension system-hardening System-Health system-integrity system-maintenance system-programming System-Programming system-prompts System-Security systemd systems-programming tailscale taints targeted-attacks TCC tcp TCP team-collaboration telegram templates temporal-consistency Tenzir terminal terminal-services terraform tesseract testcontainers testing Testing tetragon text-classification text-to-image textract thm-blue-teaming thm-write-up-apiwizards-breach thm-write-up-benign thm-write-up-blizzard thm-write-up-block thm-write-up-boogeyman-1 thm-write-up-boogeyman-2 thm-write-up-boogeyman-3 thm-write-up-carnage thm-write-up-dead-end- thm-write-up-disgruntled thm-write-up-disk-analysis-&-autopsy thm-write-up-diskfiltration thm-write-up-diskrupt thm-write-up-exfilnode thm-write-up-hunt-me-i-payment-collectors thm-write-up-investigating-with-splunk thm-write-up-ironshade thm-write-up-itsybitsy thm-write-up-juicy-details thm-write-up-memory-forensics thm-write-up-new-hire-old-artifacts thm-write-up-parrotpost-phishing-analysis thm-write-up-phishing-analysis-tools-(case-1-3) thm-write-up-ps-eclipse thm-write-up-retracted thm-write-up-revil-corp thm-write-up-secret-recipe thm-write-up-slingshot thm-write-up-snapped-phish-ing-line thm-write-up-tardigrade thm-write-up-tempest thm-write-up-the-greenholt-phish thm-write-up-unattended threading Threat Detection Threat Hunting Threat Mitigation threat-detection Threat-Detection threat-hunting Threat-Hunting threat-intelligence Threat-Intelligence threat-landscape threat-modeling threat-prevention threat-remediation threshold-cryptography tiktok time-series timeout tinyemu tls TLS toast-notifications token-flow tokio Tokio tolerations tomcat tool-poisoning tool-using-agents tools tpami tpm tracee tracing Traffic-Analysis traffic-routing training-data-extraction training-data-reconstruction training-efficiency transformers trojan trojan-attacks troubleshooting trusted-execution trusted-execution-environments trusted-publishing trustworthiness tryhackme tunnel tunneling tunnels turing TURN Turnstile tutorial Tutorial twitter typescript ubuntu Ubuntu udev udp UDP ui UI UI Testing ULS umci uncertainty Unified-Logging uninstallation unintended-behaviors unisolation Unit Testing unix utilities unlisted-labs upgrade upgrade-issues uprobes usb-control USB-Monitoring Use Cases use-cases usecurity user accounts user-experience User-Experience User-Mode user-monitoring user-session User32 utm-stack valtown vault Vector-Database vega vendor-lock-in verification version control version-control Version-Control Video video video-chat video-models video-safety ViewModel vim violation-detection virtualization Virtualization VirusTotal Visual-Studio visualization Visualization vldb vmware volumes vpc vpn vpn-replacement vscode vsftp vulnerabilities vulnerability vulnerability-analysis Vulnerability-Assessment vulnerability-detection Vulnerability-Management vulnerability-scanning vulnlab waf WAF warp WARP WASAPI wasi wasm watermarking wazuh Wazuh wdac web Web Development web-acceleration web-components web-development web-filtering web-infrastructure web-performance web-security Web-Security web-servers webassembly WebAssembly webauthn WebAuthn webhooks webrtc WebRTC websockets white-labeling wikipedia Win32 windbg windows Windows windows admin center Windows API Windows SDK Windows Security Windows Server 2019 Windows Services windows updates windows-10-iot windows-api Windows-API Windows-Firewall Windows-Forensics windows-monitoring Windows-SDK windows-security Windows-Security winexe winrt WinSock wmi WMI wmic wordpress worker-nodes workers Workers workflow Workflow workflow-automation Workflow-Automation workload-identity WQL wsl xdp XDP XDR xdr xdr-platform xdr-testing xpc xprotect XProtect xsl yaml YARA youtube zeit-now zero-copy zero-day zero-trust Zero-Trust zsh ztna

129 words

1 minute

Lag window function in SQLite

Anubhav Gain

2024-05-22

/

/

sqlite

/

git

/

database

/

web

Lag window function in SQLite#

Here’s how to use a lag window function to calculate new cases per day when the table just has total cases over time on different dates.

The key clause is this:

1
select
2
  day,
3
  confirmed - lag(confirmed, 1) OVER (
4
    ORDER BY
5
      day
6
  ) as new_cases

So the syntax is lag(column, 1) over (order by day) - to get the previous value of column based on the day.

Full example query (using a CTE as well):

1
with italy as (
2
  select
3
    rowid,
4
    day,
5
    country_or_region,
6
    province_or_state,
7
    admin2,
8
    fips,
9
    confirmed,
10
    deaths,
11
    recovered,
12
    active,
13
    latitude,
14
    longitude,
15
    last_update,
16
    combined_key
17
  from
18
    johns_hopkins_csse_daily_reports
19
  where
20
    "country_or_region" = :p0
21
  order by
22
    confirmed desc
23
)
24
select
25
  day,
26
  confirmed - lag(confirmed, 1) OVER (
27
    ORDER BY
28
      day
29
  ) as new_cases
30
from
31
  italy
32
order by day desc limit 50

Originally tweeted here: https://twitter.com/simonw/status/1246482954630492200

Lag window function in SQLite

https://mranv.pages.dev/posts/lag-window-function-in-sqlite/

Author

Anubhav Gain

Published at

2024-05-22

License

CC BY-NC-SA 4.0

Packaging a Python CLI tool for Homebrew

Running Datasette on Replit

Lag window function in SQLite