Tagged articles
Window Functions
98 articles · Page 1 of 1
This article explains ten advanced SQL concepts — including CTEs, recursive CTEs, temporary functions, pivoting with CASE WHEN, EXCEPT vs NOT IN, self joins, ranking functions, delta calculations with LAG/LEAD, running totals, and datetime manipulation — with practical code examples for data science interview preparation.
CTEDateTime ManipulationLAG
0 likes · 11 min read

This article covers 10 advanced SQL concepts — CTEs, recursive CTEs, temporary functions, CASE WHEN pivoting, EXCEPT vs NOT IN, self-joins, ranking functions, delta calculations, running totals, and datetime manipulation — with practical code examples for data science interviews.
CTEData Science InterviewsDateTime Manipulation
0 likes · 11 min read

This article details SQLite's SQL enhancements from versions 3.22.0 to 3.26.0 in 2018, covering boolean literals with IS [NOT] TRUE/FALSE, window functions with OVER clause limitations, FILTER clause for aggregates, UPSERT via INSERT ON CONFLICT, and column renaming via ALTER TABLE RENAME COLUMN.
FILTER clauseSQLSQL standards
0 likes · 12 min read

This article explains ten advanced SQL concepts including CTEs, recursive queries, temporary functions, pivoting with CASE WHEN, EXCEPT vs NOT IN, self-joins, ranking functions, delta calculations with LEAD/LAG, running totals, and datetime manipulation, with code examples for data science interview preparation.
CTEDateTime ManipulationPivoting
0 likes · 12 min read

This article showcases a collection of impressive SQL queries—including a PostgreSQL Tetris implemented with a recursive CTE, window‑function session analysis, a ClickHouse real‑time funnel, dynamic WHERE clause generation, and a recursive employee hierarchy—while discussing performance tips and engine choices.
ClickHouseHivePostgreSQL
0 likes · 25 min read

This article presents ten practical MySQL advanced techniques—including EXPLAIN analysis, composite and covering indexes, index condition push‑down, prefix indexes, window functions, CTEs, JSON handling, partition tables, join optimizations, variables, online DDL, and generated columns—each illustrated with real‑world SQL examples and execution‑plan screenshots to boost efficiency in high‑volume, high‑concurrency environments.
Advanced SQLCTEIndex Optimization
0 likes · 16 min read

This article walks through ten high‑level MySQL tricks—including EXPLAIN analysis, advanced indexing, window functions, CTEs, JSON handling, partitioning, join optimizations, user‑defined variables, online DDL, and generated columns—showing concrete examples, performance trade‑offs, and step‑by‑step guidance for turning slow queries into lightning‑fast operations.
CTEMySQLPartitioning
0 likes · 15 min read

This article reviews MySQL's GROUP BY fundamentals, highlights its limitations and execution order, then demonstrates how window functions—including ROW_NUMBER, RANK, DENSE_RANK, cumulative totals, moving averages, and TOP N techniques—can overcome those gaps for richer analytical queries.
MySQLRankingSQL
0 likes · 11 min read

This article presents eleven practical MySQL advanced techniques—including EXPLAIN analysis, sophisticated indexing, window functions, CTEs, JSON handling, partitioning, join optimizations, user variables, online DDL, and generated columns—to help developers dramatically improve query performance and scalability in high‑load environments.
Advanced QueriesJSONMySQL
0 likes · 18 min read

This article reveals eight powerful, lesser‑known MySQL 8.0 features—including window functions, descending indexes, generated columns, invisible indexes, optimizer hints, resource groups, LATERAL JOIN, and multi‑valued JSON indexes—showing how they dramatically improve query performance and simplify complex SQL tasks.
LATERAL JOINResource GroupSQL Optimization
0 likes · 12 min read

This article reviews SQLite's 2018 enhancements, covering boolean literals, window functions, the FILTER clause, upsert syntax, column‑renaming support, and related API changes, while also noting the remaining limitations compared with other major database systems.
Boolean LiteralsSQLSQLite
0 likes · 10 min read

This article presents a comprehensive guide to advanced MySQL techniques, covering CTEs, window functions, and query optimization strategies, complete with step‑by‑step code examples, performance tips, and practical demonstrations such as ranking employees and creating materialized views.
CTEMySQLQuery Performance
0 likes · 9 min read

This article presents ten advanced SQL concepts—including CTEs, recursive CTEs, temporary functions, CASE‑WHEN pivots, EXCEPT vs NOT IN, self‑joins, ranking window functions, delta calculations, cumulative sums, and date‑time manipulation—each explained with concrete examples and code snippets.
CTEData PivotRanking Functions
0 likes · 11 min read

This article presents ten powerful SQL techniques—including CTEs, recursive CTEs, temporary functions, CASE‑WHEN pivots, EXCEPT vs NOT IN, self‑joins, ranking functions, delta calculations, cumulative sums, and datetime manipulation—complete with explanations and runnable code examples for data analysts and engineers.
CTEWindow Functionsdata analysis
0 likes · 13 min read

Discover ten powerful MySQL features—including CTEs, window functions, conditional aggregation, JSON handling, generated columns, and UPSERT tricks—illustrated with real‑world examples and performance tips, so you can write SQL that is more efficient, maintainable, and production‑ready.
CTEGenerated ColumnsJSON
0 likes · 15 min read

Discover eight powerful, lesser‑known MySQL 8.0 features—including window functions, descending indexes, generated columns, invisible indexes, hints, resource groups, LATERAL JOIN, and multi‑valued JSON indexes—that can dramatically improve query performance, simplify code, and give you an edge in interviews and production environments.
Generated ColumnsJSONLATERAL JOIN
0 likes · 12 min read

This article presents ten practical MySQL interview questions covering consecutive‑login detection, efficient pagination, top‑salary per department, customers without orders, cumulative sales, recursive cycle detection, pivoting rows to columns, duplicate removal, and user retention, each with complete SQL statements, underlying principles, and performance considerations.
CTEInterview questionsMySQL
0 likes · 11 min read

This article presents seven practical DuckDB SQL patterns—querying files directly, treating partition folders as tables, deduplicating with QUALIFY, computing rolling metrics with window functions, pivot/unpivot, handling JSON arrays, and exporting results to Parquet—plus tips and a mini case study that show how to turn a notebook into a lightweight OLAP engine without leaving the Python environment.
DuckDBJSONParquet
0 likes · 12 min read

This article presents ten practical MySQL techniques—from using JSON columns for flexible data to leveraging CTEs, window functions, and advanced clauses like WITH ROLLUP and INSERT IGNORE—each illustrated with real‑world scenarios and ready‑to‑run SQL code to improve query efficiency and developer productivity.
CTEDatabase QueriesJSON
0 likes · 11 min read

SQLite, often dismissed as a toy, proves its robustness by adding powerful SQL capabilities in 2018—including boolean literals, window functions, FILTER clauses, upsert syntax, and column renaming—making it a viable choice for production workloads despite lacking a network layer.
New FeaturesSQLSQLite
0 likes · 10 min read

This article explores SQLite's 2018 enhancements—including boolean literals, window functions, FILTER clauses, upsert syntax, and column renaming—detailing how each feature works, its syntax, and practical code examples, while also clarifying limitations compared to other major database systems.
Boolean LiteralsFILTER clauseSQL
0 likes · 10 min read

Learn how MySQL window functions—row_number, rank, and dense_rank—can efficiently rank and rank‑tie data such as class scores or sales amounts, with clear syntax examples, differences in handling duplicate values, and practical SQL queries illustrated with real‑world scenarios.
MySQLROW_NUMBERRanking
0 likes · 6 min read

This article reviews SQLite's 2018 enhancements, covering Boolean literals, window functions, the FILTER clause, upsert syntax, column renaming, and related API changes, with code examples and comparisons to other databases, highlighting its reliability and suitability for production use.
Boolean LiteralsSQLSQLite
0 likes · 11 min read

This article explores the latest SQLite SQL enhancements introduced between versions 3.22.0 and 3.26.0 in 2018, covering boolean literals, window functions, FILTER clauses, upsert syntax, column renaming, and related API changes, with code examples and practical usage notes.
SQLSQLiteUPSERT
0 likes · 10 min read

This article examines LeetCode problem 185 – the hardest SQL challenge – by preparing the Employee and Department tables, presenting two solution approaches (a subquery and a window‑function version), benchmarking their performance on large test data, and explaining why window functions are the superior choice in MySQL 8+.
LeetCodeMySQLSQL
0 likes · 10 min read

This article outlines ten crucial intermediate‑to‑advanced SQL techniques—including common table expressions, recursive CTEs, temporary functions, CASE‑WHEN pivots, EXCEPT vs NOT IN, self‑joins, ranking window functions, delta calculations, cumulative sums, and date‑time manipulation—providing explanations and practical query examples for data professionals.
CTEWindow Functionsdatabase
0 likes · 11 min read

This article provides a comprehensive guide to essential SQL concepts for backend developers and data engineers, covering query execution order, core commands, various join types, common table expressions (including recursive CTEs), window functions, and performance optimization techniques such as table scans and indexing, complete with practical code examples and visual illustrations.
CTEJoinsSQL
0 likes · 10 min read

This article presents ten essential advanced SQL techniques—including EXPLAIN, window functions, CTEs, bulk operations, index optimization, transactions, JSON handling, temporary tables, proper NULL handling, and recursive queries—to help developers write more efficient, maintainable, and powerful database code.
Advanced QueriesCTESQL
0 likes · 8 min read

This article demonstrates three SQL techniques—DISTINCT, GROUP BY, and the ROW_NUMBER window function—for deduplicating records and counting unique tasks, comparing their syntax, performance, and behavior across MySQL, Hive, and Oracle environments.
DISTINCTHiveOracle
0 likes · 5 min read


ITPUBMar 21, 2025 · Databases
This article reviews the SQL enhancements introduced in SQLite from version 3.22.0 through 3.26.0, covering boolean literals, window functions, the FILTER clause, upsert syntax, and column‑renaming support, while comparing SQLite's capabilities to other major database engines.
Boolean LiteralsFILTER clauseRename Column
0 likes · 11 min read

This article explains the concept and syntax of SQL window functions, demonstrates basic and advanced use cases with step‑by‑step data preparation, queries using row_number() and lag, and provides complete scripts to solve common interview‑style ranking problems.
SQLSQL InterviewWindow Functions
0 likes · 8 min read

The article walks through a common database requirement—grouping rows, sorting within each group, and extracting the first (or top N) records—by preparing sample data, comparing MySQL window functions, subqueries, and JOIN solutions, adding index optimizations, and demonstrating equivalent Elasticsearch aggregations, all backed by concrete performance measurements.
ElasticsearchMySQLSQL
0 likes · 11 min read

This article presents a MySQL interview case that examines e‑commerce repurchase behavior using orders and users tables, defines repeat purchasers, computes average repurchase intervals and amounts, identifies high‑value customers, and aggregates results by city through CTEs, window functions, and grouped statistics.
CTECity-Level MetricsE-commerce Analytics
0 likes · 8 min read

The article shows how to use MySQL CTEs and window functions to identify e‑commerce users who spent over 10,000 CNY in 2023, calculate their average spend and order count, determine their share of all users, and break the results down by city.
CTEInterviewMySQL
0 likes · 5 min read

This article presents an e‑commerce interview case that defines three tables (orders, order_items, products), loads sample data, and walks through a step‑by‑step SQL solution using CTEs, aggregation, window functions and HAVING to calculate each customer's total spend, most expensive order, and favorite product category, filtering and sorting the results.
CTEInterviewMySQL
0 likes · 9 min read

This article explains Spark SQL's window function fundamentals, introduces two key optimizations—Offset Window Frame and Infer Window Group Limit—and provides a detailed Q&A covering implementation details, execution plan impacts, and underlying architecture.
Apache SparkOptimizationSQL Performance
0 likes · 13 min read

This guide explains how to overcome MySQL analytical performance bottlenecks by integrating AnalyticDB MySQL with Data Transmission Service (DTS), covering architecture, resource setup, test data creation, real‑time synchronization, and high‑performance SQL queries using window functions.
AnalyticDBCloud Data WarehouseDTS
0 likes · 18 min read

This guide walks through ten essential advanced SQL concepts—including CTEs, recursive queries, temporary functions, CASE‑based pivoting, EXCEPT vs NOT IN, self‑joins, ranking functions, delta calculations, cumulative totals, and date‑time manipulation—providing clear explanations and runnable examples to help data‑science professionals ace interview challenges.
Advanced QueriesCTESQL
0 likes · 11 min read

This guide walks through ten essential advanced SQL techniques—including CTEs, recursive CTEs, temporary functions, CASE‑WHEN pivots, EXCEPT vs NOT IN, self‑joins, ranking functions, delta calculations with LAG/LEAD, cumulative sums, and date‑time manipulation—to help data professionals ace interview challenges and write cleaner, more powerful queries.
Advanced SQLCTESQL
0 likes · 11 min read

This article reviews the SQL enhancements added to SQLite between versions 3.22.0 and 3.26.0, covering boolean literals, window functions, the FILTER clause, INSERT … ON CONFLICT (upsert), column renaming, and related API updates.
Boolean LiteralsFILTER clauseSQL
0 likes · 10 min read

This article presents ten crucial intermediate‑to‑advanced SQL concepts—including CTEs, recursive queries, temporary functions, CASE pivots, EXCEPT vs NOT IN, self‑joins, ranking functions, delta calculations, cumulative totals, and date‑time manipulation—each explained with clear examples and code snippets.
CTEData PivotRanking
0 likes · 11 min read

This article presents ten essential advanced MySQL concepts—including CTEs, recursive CTEs, temporary functions, CASE‑WHEN pivots, EXCEPT vs NOT IN, self‑joins, ranking window functions, delta calculations, running totals, and date‑time manipulation—each explained with clear examples and practical SQL snippets.
CASE WHENCTEDATETIME
0 likes · 11 min read

This article explains ten advanced SQL techniques—including common table expressions, recursive CTEs, temporary functions, CASE‑WHEN pivots, EXCEPT vs NOT IN, self‑joins, ranking functions, delta calculations, cumulative sums, and date‑time manipulation—providing clear explanations and complete query examples to help readers ace data‑science interview questions.
CTEData Science InterviewsSQL
0 likes · 14 min read

This article explores SQLite’s 2018 SQL feature updates—from boolean literals and truth testing to window functions, FILTER clauses, upsert syntax, column renaming, and related API changes—highlighting syntax details, limitations, and practical examples for developers seeking to leverage these modern capabilities in production.
SQLSQLiteUPSERT
0 likes · 10 min read

This article reviews SQLite’s 2018 SQL enhancements—including Boolean literals, window functions, the FILTER clause, upsert syntax, and column‑renaming—explaining each feature, showing concrete code examples, comparing behavior with other databases, and noting remaining limitations.
Column RenamingFILTER clauseSQL
0 likes · 10 min read

This article introduces MaxCompute’s QUALIFY clause, explains its syntax and execution order, compares it to HAVING, provides multiple code examples—including simple and complex queries—and demonstrates how QUALIFY streamlines filtering of window function results, boosting query readability and efficiency.
MaxComputeQUALIFYSQL
0 likes · 8 min read

This article provides interview‑style SQL examples that cover creating a sales table, computing year‑over‑year and month‑over‑month growth rates, and using various window‑function techniques to identify numbers that appear consecutively at least three times.
Consecutive NumbersInterviewMOM
0 likes · 7 min read

This article explains how MySQL 8.x window functions such as OVER, PARTITION BY, and ORDER BY can simplify complex ranking and aggregation queries, demonstrates creating a sample scores table, and provides practical examples of functions like ROW_NUMBER, RANK, DENSE_RANK, NTILE, LAG, and LEAD with their results.
MySQLOVERRanking
0 likes · 18 min read

This article examines the inherent difficulties of SQL when handling multi‑step, ordered, and object‑referencing queries, illustrates them with practical examples, and introduces the Structured Process Language (SPL) as a more expressive alternative for data processing tasks.
CTESPLSQL
0 likes · 16 min read


ITPUBApr 23, 2023 · Databases
This article explains how SQL, built on relational algebra, became the standard analysis language for OLAP engines, covering its history, data models, syntax, functions, aggregation techniques, window functions, subqueries, and practical optimization considerations for modern data warehouses.
OLAPRelational AlgebraSQL
0 likes · 46 min read

This article reviews the SQLite SQL enhancements introduced between versions 3.22.0 and 3.26.0, covering boolean literals, window functions, the FILTER clause, upsert syntax, column renaming, and related API changes, while comparing SQLite’s support to other major database systems.
FILTER clauseSQLSQLite
0 likes · 10 min read

SQLite’s 2018 updates bring powerful new SQL capabilities—including true/false literals, IS TRUE/FALSE predicates, window functions, FILTER clauses, UPSERT syntax, and column‑renaming—demonstrating that this lightweight, serverless database can handle modern workloads while remaining reliable and widely deployable.
FILTER clauseSQLSQLite
0 likes · 9 min read

This article demonstrates how to create a score table in MySQL, insert sample data, and implement ranking of students using both user‑defined variables for pre‑8.0 versions and modern window functions like RANK(), DENSE_RANK() and ROW_NUMBER() to handle ties and gaps.
MySQLRankingSQL
0 likes · 7 min read

This article provides an in-depth overview of SQL window (OLAP) functions, covering their classification, syntax, window definition methods, differences from aggregate functions, and includes interview-style queries for user behavior and student score analysis.
AnalyticsInterviewSQL
0 likes · 21 min read

This article reviews SQLite's 2018 enhancements, covering Boolean literals, window functions, the FILTER clause, UPSERT syntax, column‑renaming support, and other API changes, while comparing SQLite's capabilities to other major database systems.
Boolean LiteralsFILTER clauseSQL
0 likes · 11 min read

The article investigates a Spark driver CPU overload caused by the compatiblePartitions method’s expensive permutation logic in window functions, explains the underlying O(n!) complexity, and presents a simplified implementation that eliminates the issue and has been merged into the official Spark codebase.
CPU optimizationSparkWindow Functions
0 likes · 7 min read

This guide walks you through ten essential advanced SQL techniques—including CTEs, recursive queries, temporary functions, CASE pivots, EXCEPT vs NOT IN, self‑joins, ranking window functions, delta calculations, running totals, and date‑time manipulation—providing clear explanations and practical code examples to boost your interview performance.
Advanced QueriesCTEData Science Interviews
0 likes · 12 min read


21CTOJul 27, 2022 · Databases
This article presents ten crucial intermediate‑to‑advanced SQL techniques—including CTEs, recursive CTEs, temporary functions, CASE‑WHEN pivots, EXCEPT vs NOT IN, self‑joins, ranking window functions, delta calculations, cumulative sums, and date‑time manipulation—to help data professionals ace interview questions and write cleaner, more powerful queries.
CTEInterview PreparationSQL
0 likes · 11 min read

This article reviews SQLite's 2018 enhancements, covering Boolean literals, window functions, the FILTER clause, upsert support, column renaming, and related API changes, while highlighting limitations and compatibility notes compared with other major databases.
Boolean LiteralsSQLSQL features
0 likes · 12 min read

The article explains SQLite’s 2018 enhancements—including boolean literals, window functions, FILTER clause, UPSERT syntax, and column‑renaming—detailing how these features work, their syntax, limitations, and providing code examples for developers seeking to use SQLite in production environments.
FILTER clauseSQLSQLite
0 likes · 10 min read

This article presents ten core SQL techniques—including CTEs, recursive CTEs, temporary functions, CASE WHEN pivots, EXCEPT vs NOT IN, self‑joins, ranking window functions, delta calculations, cumulative sums, and date‑time manipulation—each explained with clear descriptions and practical query examples to help candidates ace data‑analysis interviews.
CASE WHENCTERanking
0 likes · 11 min read

SQLite, often underestimated, introduced several powerful SQL enhancements in 2018—including boolean literals, window functions, FILTER clauses, UPSERT syntax, and column renaming—bringing its capabilities closer to major RDBMSes while retaining its lightweight, file‑based design.
Boolean LiteralsSQLSQLite
0 likes · 11 min read

The article reviews SQLite's 2018 enhancements—including boolean literals, window functions, FILTER clause, UPSERT syntax, and column‑renaming—explaining their syntax, behavior with NULLs, and practical examples, while also noting SQLite's reliability and deployment advantages despite lacking a network layer.
Boolean LiteralsFILTER clauseSQL
0 likes · 10 min read

This article reviews the SQLite SQL enhancements introduced between versions 3.22.0 and 3.26.0 in 2018, covering boolean literals, window functions, FILTER clauses, UPSERT syntax, column renaming, and related API changes with code examples and practical notes.
SQLSQLiteUPSERT
0 likes · 10 min read

This article reviews the SQLite enhancements introduced between versions 3.22.0 and 3.26.0 in 2018, covering Boolean literals, window functions, the FILTER clause, UPSERT syntax, column renaming, and related API changes, while comparing them to other major database systems.
Boolean LiteralsFILTER clauseSQL
0 likes · 11 min read

SQLite, often underestimated, has added powerful SQL capabilities in 2018—including true/false literals, advanced boolean checks, window functions, FILTER clauses, upsert support via INSERT ON CONFLICT, and column renaming—bringing its feature set closer to major databases while retaining its lightweight, file‑based architecture.
Boolean LiteralsSQLSQLite
0 likes · 12 min read

This article presents ten essential advanced SQL techniques—including CTEs, recursive CTEs, temporary functions, CASE‑WHEN pivots, EXCEPT vs NOT IN, self‑joins, ranking windows, delta calculations, cumulative totals, and date‑time manipulation—each explained with clear examples and code snippets for interview preparation.
CASE WHENCTEData Science Interviews
0 likes · 11 min read

This article explains what SQL window functions are, shows their syntax, and provides step‑by‑step basic and advanced examples—including how to rank rows within groups and reset rankings when key columns change—complete with runnable SQL scripts and result screenshots.
RankingSQLSQL Tutorial
0 likes · 8 min read

This article explains three common SQL deduplication techniques—using DISTINCT, GROUP BY, and the ROW_NUMBER window function—illustrating their syntax, performance considerations, and example queries for counting unique task identifiers in relational databases.
DISTINCTROW_NUMBERSQL
0 likes · 4 min read

This article explains the usage of four MySQL 8.0 window functions—first_value, last_value, nth_value, and ntile—by describing their purpose, the required window frame syntax, and providing concrete SQL examples with sample data.
MySQLWindow Functionsfirst_value
0 likes · 8 min read

Kuaishou dramatically increased Flink SQL adoption, introduced Group Window Aggregate and Window TVF extensions, applied aggregation state reuse and mini‑batch techniques, and enhanced stability through data‑skew mitigation and aggregate‑state compatibility, outlining future plans for streaming and batch SQL improvements.
FlinkSQLState Optimization
0 likes · 19 min read

This article presents three practical SQL interview problems—finding the second‑highest salary per department, counting login intervals under five days, and analyzing daily purchase channels—complete with data setup, solution queries, and key concepts such as window functions and joins.
Interview questionsMySQLSQL
0 likes · 12 min read

This article explains how to remove duplicate rows in SQL by using DISTINCT, GROUP BY, and the ROW_NUMBER window function, compares their syntax and performance, and provides concrete examples with a Task table and a Test helper table.
DISTINCTROW_NUMBERSQL
0 likes · 4 min read

This article provides a comprehensive guide to Flink Table and SQL window semantics—including group, tumbling, sliding, and session windows—covers over windows, demonstrates how to define windows in SQL, explains built‑in functions, shows how to implement scalar, table, aggregate and table‑aggregate UDFs, and details Flink's integration with Hive, complete with Maven dependencies and runnable examples.
FlinkHive IntegrationSQL
0 likes · 27 min read

This article walks through MySQL 8.0’s SQL optimizer, explaining how a raw SQL statement is parsed, transformed, and optimized through stages such as Setup, Resolve, Derived‑table merging, materialization, window‑function handling, and ROLLUP processing, illustrated with diagrams and code examples.
MySQLRollupSQL Optimizer
0 likes · 20 min read

MySQL 8.0 introduces several relational‑database enhancements, including invisible indexes for performance tuning, persistent configuration via SET PERSIST, default utf8mb4 character set, support for Common Table Expressions, and window functions, each illustrated with practical SQL examples.
8.0CTEMySQL
0 likes · 7 min read

This article, presented by Tencent senior engineer Du Li, details the current state of Flink SQL, compares Jar, Canvas, and SQL modes, introduces window‑function extensions, retract‑stream optimizations, and outlines future roadmap plans for cost‑based optimization and new features in the real‑time computing platform.
FlinkRetract StreamSQL
0 likes · 19 min read

This article explains MySQL 8.0 window function FRAME clause syntax, differentiates ROWS and RANGE units, and demonstrates various frame boundaries such as CURRENT ROW, UNBOUNDED PRECEDING/FOLLOWING, and expression‑based preceding/following with concrete query examples.
FRAME ClauseMySQLSQL
0 likes · 10 min read

This article introduces several MySQL 8 enhancements—including invisible indexes for performance testing, the SET PERSIST command for durable configuration changes, default UTF8MB4 encoding, Common Table Expressions for clearer queries, and window functions for ranking and aggregation—illustrated with practical SQL examples.
CTEMySQLWindow Functions
0 likes · 6 min read

This article explains why SQL queries do not start with SELECT, outlines the typical logical execution order of clauses such as FROM, WHERE, GROUP BY, HAVING, SELECT, ORDER BY, and LIMIT, and discusses how database engines may reorder operations for optimization, with code examples and comparisons to LINQ and pandas.
LINQPandasSQL
0 likes · 6 min read

This article explains MySQL 8.0 window functions, provides clear definitions, demonstrates basic and advanced usage with sample tables and SQL queries, and lists all available window functions, helping readers apply ranking and cumulative calculations in real‑world database scenarios.
AnalyticsMySQLSQL
0 likes · 8 min read

Discover the true execution sequence of SQL queries, why SELECT runs fifth, how window functions and GROUP BY interact, and the impact of engine optimizations, illustrated with diagrams and code examples spanning aliases, LINQ, and pandas.
LINQPandasSQL
0 likes · 6 min read

Although most SQL statements begin with SELECT, the actual execution follows a specific order—WHERE, GROUP BY, HAVING, then SELECT—illustrated by a diagram that also clarifies common misconceptions about filtering window functions, column aliases, and how databases may reorder operations for optimization.
LINQOptimizationPandas
0 likes · 6 min read


ITPUBJun 19, 2020 · Databases
This guide explains how SQL analytic (window) functions work, covering the concepts of current row, logical and physical windows, PARTITION, ORDER BY, the differences between ROWS and RANGE, window clause variations, and their advantages and drawbacks.
Analytic FunctionsOracleSQL
0 likes · 12 min read

This article explains the actual logical execution order of SQL statements, clarifies common misconceptions about WHERE, GROUP BY, and SELECT, shows how column aliases affect processing, discusses optimizer reordering, and compares the order with LINQ and pandas query patterns.
LINQOptimizationPandas
0 likes · 7 min read

This article introduces Spark SQL window functions, explains the difference between aggregation and window functions, and demonstrates how to use various ranking functions such as ROW_NUMBER, RANK, DENSE_RANK, and NTILE with practical Scala code examples and partitioning options.
RankingSQLScala
0 likes · 9 min read

This article introduces Apache Flink's Streaming SQL, explains its theoretical foundations such as the table‑stream relationship and watermark semantics, describes the platform's practical enhancements—including source/sink wrappers, built‑in functions, and native Retract Stream support—and showcases several real‑time computation examples.
Apache FlinkDataStreamReal-time Computing
0 likes · 31 min read

The article explains that SQL queries are not executed in the order written—SELECT is evaluated fifth after FROM, WHERE, GROUP BY, and HAVING—illustrates this logical sequence with diagrams, discusses alias handling, optimizer reordering, and shows comparable patterns in LINQ, pandas, and dplyr.
OptimizationSQLWindow Functions
0 likes · 6 min read

This article demonstrates how to efficiently remove duplicate rows from a million‑record MySQL table by comparing created_time and item_name, exploring various approaches such as correlated subqueries, joins, user‑defined variables, index optimization, window functions, and parallel execution with shell scripts and MySQL events to achieve significant performance gains.
MySQLSQL PerformanceWindow Functions
0 likes · 21 min read

This article demonstrates how to achieve ranking and pagination in MySQL using pre‑8.0 techniques such as session variables and GROUP_CONCAT, then shows the simpler native window function syntax introduced in MySQL 8.0, with complete examples, sample data, and queries for finding low‑scoring students.
MySQLSQLWindow Functions
0 likes · 9 min read

To overcome severe performance degradation of Flink sliding windows over very long time ranges, Youzan engineers applied time‑slicing based on the greatest common divisor of window length and slide step, reducing state writes and timers, which yielded 3‑8× speedups in production.
FlinkRocksDBWindow Functions
0 likes · 18 min read

This article provides an in-depth introduction to SQL, its history and ANSI standards, then details Apache Flink's SQL capabilities—including SELECT, WHERE, GROUP BY, UNION, JOIN, window functions, and user-defined functions—accompanied by extensive code examples and a complete end‑to‑end Flink job implementation.
Apache FlinkSQLWindow Functions
0 likes · 34 min read

This tutorial explains why and how to use MySQL window functions, covering their concepts, syntax, various function families, practical examples such as ranking, distribution, lead/lag, first/last, and aggregations within dynamic windows, plus detailed SQL snippets and visual illustrations.
AnalyticsMySQLSQL
0 likes · 13 min read

This article explores PostgreSQL’s advanced SQL capabilities—including Common Table Expressions (WITH), batch inserts, the RETURNING clause, UPSERT with ON CONFLICT, TABLESAMPLE methods, aggregation functions like string_agg and array_agg, and a range of window functions—providing practical examples, performance insights, and guidance for real‑world use.
CTEPostgreSQLSQL
0 likes · 17 min read


ITPUBApr 27, 2018 · Databases
This article walks through MySQL 8.0's key relational‑database enhancements, including invisible indexes for performance testing, the SET PERSIST command for persistent configuration, default utf8mb4 encoding, Common Table Expressions for clearer queries, and window functions for advanced ranking and aggregation.
8.0CTEMySQL
0 likes · 7 min read


21CTOMar 14, 2018 · Databases
Both MySQL 8 and MariaDB 10.2 introduce robust window functions and Common Table Expressions (CTEs), enabling developers to write more efficient, maintainable queries; this article explains their syntax, demonstrates practical examples, compares implementations, and highlights performance and readability benefits across popular database systems.
CTEMariaDBMySQL
0 likes · 13 min read

This article introduces MySQL 8.0.2's window functions, demonstrates how to compute yearly average ratings and rank movies by year and overall using examples with sample tables, and explains the syntax and benefits over traditional GROUP BY queries.
MySQLSQLWindow Functions
0 likes · 7 min read


ITPUBAug 29, 2016 · Databases
This article explains how to extract the top N records for each group in PostgreSQL, compares the slow window‑function approach with a fast recursive‑CTE and set‑returning function solution, and shows performance results dropping from over 20 seconds to under half a second.
PostgreSQLRecursive CTETop N per group
0 likes · 8 min read
