Oracle SQL

Number the rows of each group with ROW_NUMBER for top-N-per-group queries and deduplication, and make the numbering repeatable.

Answer top-N questions such as the busiest routes or best-selling cabins quickly with APPROX_COUNT, APPROX_SUM, and APPROX_RANK.

Estimate medians and percentiles of very large data quickly, make the result repeatable with DETERMINISTIC, and check its accuracy.

Estimate the number of distinct values in huge tables quickly, and store mergeable sketches so distinct counts add up correctly.

Detect whether data changed or differs between databases by comparing order-independent checksums of column values over groups.

Combine flag integers bit by bit over a group to find options common to all rows or available in any, and decode them with BITAND.

Check whether all or any rows of a group are true, such as all passengers checked in, with BOOLEAN_AND_AGG, BOOLEAN_OR_AGG, and EVERY.

Get values from the first or last row of each group, such as the earliest hire's salary, with KEEP (DENSE_RANK FIRST ORDER BY ...).

Describe the shape of your data beyond mean and spread: measure lopsidedness with SKEWNESS and the weight of the tails with KURTOSIS.

Fit a least-squares line through pairs of values with REGR_SLOPE and REGR_INTERCEPT, measure the fit with REGR_R2, and predict values.

Measure whether two columns rise and fall together with COVAR_POP and COVAR_SAMP, and see how units change covariance but not correlation.

Measure how closely two columns move together with Pearson, Spearman, and Kendall correlation, and why per-group results can differ.

Measure the spread of each group as the mean squared deviation with VARIANCE, and choose between the population and sample versions.

Measure how spread out the values of each group are with STDDEV, and choose between the population and sample versions correctly.

Get percentiles that are actual values from the data with PERCENTILE_DISC, and see how they differ from interpolated PERCENTILE_CONT.

Compute medians, quartiles, and 90th percentiles of each group with PERCENTILE_CONT, which interpolates between the nearest values.

Find the most frequent value in each group, such as the usual base airport or cabin, with STATS_MODE, and know how it handles ties.

Find the middle value of each group with MEDIAN, and see why it describes skewed data such as salaries better than the average.