SQL Aggregate vs Window Functions: Key Differences Explained

·1 views

SQL aggregate functions and window functions are both used to perform calculations on sets of data, but they differ significantly in how they return results. Aggregate functions such as SUM(), AVG(), COUNT(), MAX(), and MIN() collapse multiple rows into a single scalar value. Window functions, by contrast, perform calculations across a related set of rows while preserving each individual row in the output. For example, the RANK() window function can rank employees by salary without reducing the result set to a single row. Understanding this distinction helps developers choose the right function type depending on whether row-level detail needs to be retained.

Read the full story at DEV Community

This is an AI-generated summary. ShortSingh links to the original source for the complete article.

Discussion (0)

Docker Model Runner Enables Contract Testing Across Three AI API Formats Locally

Docker Model Runner exposes OpenAI-, Anthropic-, and Ollama-compatible API formats through a single local model environment, making it a practical target for testing protocol adapters without relying on remote providers. Developers can define a shared adapter interface that normalizes responses across all three formats, isolating domain code from provider-specific wire details. Contract tests can then verify properties the application actually depends on, such as non-empty responses, finish reasons, error shapes, and streaming behavior, rather than assuming API compatibility from a shared base URL alone. The approach also recommends explicitly declaring adapter capabilities, such as tool support or token usage availability, so applications can fail fast when a feature is unsupported rather than encountering silent failures mid-execution. Differences in request translation and sampling mean generated text may still vary across adapters even when all three target the same underlying model.

0 comments Read more at DEV Community

ProgrammingDEV Community ·

SMTP 250 OK Is Not Enough: 24% of Pre-Validated Emails Still Bounced

A developer tested 50 email addresses on August 26 that had already passed both syntax checks and live SMTP handshakes returning 250 OK responses, yet 12 of them resulted in hard bounces within 38 minutes. The 24% failure rate highlights a critical gap in common email validation pipelines that treat a 250 OK response as a deliverability guarantee. To investigate further, the developer ran the same addresses through a third-party validation API that checks for breach status, role addresses, catch-all configurations, and greylisting. The API returned an smtp_verified value of null rather than true for a Gmail address, honestly reflecting that domain-level acceptance does not confirm individual inbox reachability. The findings suggest that multi-layered email validation — beyond SMTP handshakes alone — is necessary to reduce bounce rates in production sending pipelines.

0 comments Read more at DEV Community

ProgrammingDEV Community ·

Why Real-Time Face Swap Is a Harder Engineering Problem Than Offline Video

Real-time and offline face swap may use similar AI techniques, but their engineering challenges differ fundamentally due to strict per-frame deadlines in live processing. Offline pipelines can afford to retry difficult frames, use future frames for context, and take as long as needed, while real-time systems must deliver output continuously or risk latency, dropped frames, or reduced quality. Temporal consistency is a key challenge in real-time face swap, as small frame-to-frame variations in skin tone, geometry, or mask edges can produce visible flicker or identity drift when played back at video speed. Balancing responsiveness and smoothing adds another layer of complexity, since reacting too quickly causes instability while over-smoothing causes the output to lag behind the subject's movements. Cloud-based inference introduces additional variables such as network jitter and scheduling delays, meaning real-time face swap quality must account for visual fidelity, throughput, latency, and stability all at once rather than optimizing for a single metric.

0 comments Read more at DEV Community

ProgrammingDEV Community ·

AI Agents Need Independent Reviewers, Not Self-Checks, Developer Finds

A developer migrating an enterprise healthcare platform across four major Angular versions (11 to 15) used a structured multi-agent system to manage AI-assisted code changes safely. The system separated roles into an executor agent, a reviewer agent working in a separate context, a test agent, and a visual regression agent — with no agent permitted to validate its own output. This design was inspired by accounting and audit controls, such as the principle that the person who writes a check should not also sign it. The developer observed that a reviewer agent evaluating code it did not produce behaved differently and flagged substantive issues that led to real changes. The key takeaway is that independent review — whether human or AI — reduces the risk of an agent hiding errors rather than genuinely fixing them.

0 comments Read more at DEV Community