DataQuery.inAI
140+ Pre-Configured Functions & ML Models

The DataQuery Function Library

Search curated transformations across Python Pandas, DuckDB SQL, Power BI DAX, Statistics, PySpark, Hadoop, and 16 Machine Learning algorithms explained simply. Click to test on your own data instantly.

PYTHONbasic

df.head(n)

Return the first n rows of the dataframe.

df.head(5)
PYTHONbasic

df.tail(n)

Return the last n rows of the dataframe.

df.tail(5)
PYTHONbasic

df.info()

Summary of column datatypes and memory usage.

df.info()
PYTHONbasic

df.describe()

Summary statistics (mean, std, min, max, percentiles).

df.describe()
PYTHONbasic

df.shape

Tuple showing total row and column dimensions.

print(df.shape)
PYTHONbasic

df.columns.tolist()

List of all column identifiers.

df.columns.tolist()
PYTHONbasic

df.rename()

Rename one or multiple dataset columns.

df.rename(columns={'revenue': 'total_revenue'})
PYTHONbasic

df.drop(columns=[])

Drop specified columns from the active view.

df.drop(columns=['unneeded_col'])
PYTHONbasic

df.dropna()

Eliminate rows containing missing/null values.

df.dropna(subset=['revenue'])
PYTHONbasic

df.fillna()

Impute missing null values with constant or median.

df['profit'].fillna(0)
PYTHONintermediate

df.groupby().agg()

Aggregate metric sums and averages by categorical dimensions.

df.groupby('region')['revenue'].sum().reset_index()
PYTHONintermediate

df.sort_values()

Sort records ascending or descending by target metrics.

df.sort_values(by='revenue', ascending=False)
PYTHONintermediate

Calculated Ratio

Create computed column dividing two numeric fields.

df['margin_pct'] = df['profit'] / df['revenue']
PYTHONintermediate

pd.to_datetime()

Parse date string representations into datetime objects.

df['order_date'] = pd.to_datetime(df['order_date'])
PYTHONintermediate

df.query()

Filter dataframe rows via concise string expressions.

df.query('revenue > 10000 & region == "Europe"')
PYTHONintermediate

df['col'].value_counts()

Distribution frequencies of categorical attributes.

df['category'].value_counts()
PYTHONintermediate

df.drop_duplicates()

Remove duplicate entity rows by key identifier.

df.drop_duplicates(subset=['order_id'])
PYTHONintermediate

pd.merge()

Join two tables with SQL-like left or inner join.

pd.merge(df1, df2, on='customer_id', how='left')
PYTHONintermediate

df.pivot_table()

Cross-tabulation pivot matrix of values.

df.pivot_table(index='region', columns='category', values='revenue', aggfunc='sum')
PYTHONintermediate

df.assign()

Chain assignment of multiple computed columns.

df.assign(tax=lambda x: x.revenue * 0.15)
PYTHONadvanced

Rolling 7-Day Window

Moving average smoother over chronological series.

df.rolling(window=7, on='order_date')['revenue'].mean()
PYTHONadvanced

Inter-Order Cycle Diff

Calculate day intervals between chronological transactions.

df.groupby('customer_tier')['order_date'].diff().dt.days
PYTHONadvanced

pd.qcut() Quantile Binning

Segment dataset into statistical quintiles (RFM analysis).

pd.qcut(df['revenue'], q=5, labels=['Very Low','Low','Med','High','Top'])
PYTHONadvanced

df.corr() Pearson Matrix

Correlation coefficients between numeric metrics.

df.corr(numeric_only=True)
PYTHONadvanced

Z-Score Anomaly Detector

Identify statistical outliers exceeding 3 standard deviations.

np.where((df['revenue'] - df['revenue'].mean()) / df['revenue'].std() > 3, 'Outlier', 'Normal')
PYTHONadvanced

df.pct_change()

Period-over-period percentage expansion.

df['growth_pct'] = df['revenue'].pct_change()
PYTHONadvanced

pd.melt() Wide-to-Long

Unpivot wide matrix attributes into tidy key-value pairs.

pd.melt(df, id_vars=['region'], value_vars=['revenue', 'cost'])
PYTHONadvanced

df.explode()

Explode nested arrays into independent rows.

df.explode('tags')
PYTHONadvanced

pd.crosstab()

Contingency frequency matrix for two categories.

pd.crosstab(df['region'], df['category'])
PYTHONadvanced

np.select() Case Multi-Branch

Vectorized case statement evaluations.

np.select([df.revenue > 20000], ['Tier 1'], default='Tier 2')
SQLbasic

SELECT Preview

Preview top records from current table.

SELECT * FROM sales LIMIT 10;
SQLbasic

COUNT(*) Aggregation

Count volume of records in table.

SELECT COUNT(*) AS total_rows FROM sales;
SQLbasic

SELECT DISTINCT

List distinct categorical values.

SELECT DISTINCT region FROM sales;
SQLbasic

WHERE Conditional Filter

Filter rows matching numeric or text predicates.

SELECT * FROM sales WHERE revenue > 10000;
SQLbasic

ORDER BY Sort

Order output records descending by revenue.

SELECT * FROM sales ORDER BY revenue DESC;
SQLbasic

GROUP BY Summation

Group records and sum numerical attributes.

SELECT region, SUM(revenue) AS total_rev FROM sales GROUP BY 1;
SQLbasic

AVG() Calculation

Calculate arithmetic mean of column.

SELECT AVG(profit) AS mean_profit FROM sales;
SQLbasic

MIN() & MAX() Bounds

Identify boundary values of numerical metric.

SELECT MIN(revenue) AS min_rev, MAX(revenue) AS max_rev FROM sales;
SQLbasic

UPPER() / LOWER()

Transform string casing for normalization.

SELECT UPPER(category) FROM sales;
SQLbasic

COALESCE() Null Fallback

Return fallback value if field is null.

SELECT COALESCE(customer_tier, 'Unclassified') FROM sales;
SQLintermediate

DATE_TRUNC('month')

Bucket chronological records into monthly intervals.

SELECT DATE_TRUNC('month', order_date) AS month, SUM(revenue) FROM sales GROUP BY 1;
SQLintermediate

HAVING Threshold Filter

Filter post-aggregation metrics.

SELECT category, SUM(revenue) FROM sales GROUP BY 1 HAVING SUM(revenue) > 50000;
SQLintermediate

CASE WHEN Logic

Conditional branch classification in SQL.

SELECT order_id, CASE WHEN revenue > 15000 THEN 'VIP' ELSE 'Standard' END AS status FROM sales;
SQLintermediate

ROUND() Decimals

Format float metric to two decimal places.

SELECT ROUND(profit / revenue * 100, 2) AS margin_pct FROM sales;
SQLintermediate

DATEDIFF('day')

Calculate elapsed calendar days between two dates.

SELECT DATEDIFF('day', start_date, end_date) FROM sales;
SQLintermediate

CONCAT() String Merge

Join multiple strings into composite identifier.

SELECT CONCAT(region, ' - ', category) AS segment FROM sales;
SQLintermediate

CAST() Type Conversion

Cast value to explicit numeric or varchar type.

SELECT CAST(revenue AS INTEGER) FROM sales;
SQLintermediate

REGEXP_MATCHES()

DuckDB regex pattern evaluator.

SELECT * FROM sales WHERE REGEXP_MATCHES(order_id, '^ORD-[0-9]+');
SQLintermediate

COUNT(DISTINCT entity)

Count unique occurrences of categorical entities.

SELECT category, COUNT(DISTINCT customer_tier) FROM sales GROUP BY 1;
SQLintermediate

Subquery Filter

Filter records using results of nested query.

SELECT * FROM sales WHERE revenue > (SELECT AVG(revenue) FROM sales);
SQLadvanced

SUM() OVER Running Total

Cumulative running total partitioned by region.

SUM(revenue) OVER (PARTITION BY region ORDER BY order_date) AS running_rev
SQLadvanced

ROW_NUMBER() Ranking

Assign sequential ranking for deduplication.

ROW_NUMBER() OVER (PARTITION BY region ORDER BY revenue DESC) AS rank
SQLadvanced

LAG() Prior Period Delta

Fetch previous row value to compute period growth.

LAG(revenue, 1) OVER (ORDER BY order_date) AS prev_rev
SQLadvanced

NTILE(4) Quartile Buckets

Divide dataset into four even statistical quartiles.

NTILE(4) OVER (ORDER BY revenue) AS quartile
SQLadvanced

WITH CTE Modular Pipeline

Common Table Expression for readable transformations.

WITH regional_rev AS (SELECT region, SUM(revenue) AS total FROM sales GROUP BY 1) SELECT * FROM regional_rev;
SQLadvanced

QUALIFY Deduplication

DuckDB native window filter without nested subquery.

QUALIFY ROW_NUMBER() OVER (PARTITION BY region ORDER BY revenue DESC) = 1
SQLadvanced

APPROX_COUNT_DISTINCT()

HyperLogLog cardinality estimator for massive datasets.

SELECT APPROX_COUNT_DISTINCT(order_id) FROM sales;
SQLadvanced

MEDIAN() Exact Centroid

Calculate exact statistical 50th percentile.

SELECT MEDIAN(revenue) FROM sales;
SQLadvanced

DuckDB PIVOT Statement

Native SQL pivoting wide transformation.

PIVOT sales ON category USING SUM(revenue);
SQLadvanced

Ratio to Total OVER()

Compute each row's percentage contribution to grand total.

revenue / SUM(revenue) OVER () * 100 AS pct_of_total
DAXbasic

Total Sales = SUM()

Grand sum of column metric.

Total Sales = SUM(Sales[Revenue])
DAXbasic

Average Order Value = AVERAGE()

Mean order size.

AOV = AVERAGE(Sales[Revenue])
DAXbasic

Total Orders = COUNTROWS()

Count records in active table view.

Total Orders = COUNTROWS(Sales)
DAXbasic

Distinct Entities = DISTINCTCOUNT()

Count unique customer IDs.

Unique Customers = DISTINCTCOUNT(Sales[CustomerID])
DAXbasic

Minimum Bound = MIN()

Minimum value in field.

Min Deal = MIN(Sales[Revenue])
DAXbasic

Maximum Bound = MAX()

Maximum value in field.

Max Deal = MAX(Sales[Revenue])
DAXbasic

Safe Margin % = DIVIDE()

Safe division guarded against zero denominators.

Margin % = DIVIDE([Total Profit], [Total Sales], 0)
DAXbasic

Profit = SUM(Sales[Profit])

Total profit summation.

Total Profit = SUM(Sales[Profit])
DAXbasic

Order Year = YEAR()

Extract calendar year.

Order Year = YEAR(Sales[OrderDate])
DAXbasic

Order Month = MONTH()

Extract calendar month index.

Order Month = MONTH(Sales[OrderDate])
DAXintermediate

US Sales = CALCULATE()

Modify filter context for specific region.

US Sales = CALCULATE([Total Sales], Sales[Region] = "North America")
DAXintermediate

High Value Orders = FILTER()

Iterate table and filter records > threshold.

High Deals = COUNTROWS(FILTER(Sales, Sales[Revenue] > 10000))
DAXintermediate

All Regions = ALL()

Remove regional filter to compute denominator for shares.

All Region Sales = CALCULATE([Total Sales], ALL(Sales[Region]))
DAXintermediate

Sales Share % = DIVIDE([Total], [All])

Calculate percentage contribution to global total.

Regional Share % = DIVIDE([Total Sales], [All Region Sales])
DAXintermediate

Sales YTD = TOTALYTD()

Cumulative year-to-date time intelligence.

Sales YTD = TOTALYTD([Total Sales], Dates[Date])
DAXintermediate

Sales QTD = TOTALQTD()

Quarter-to-date aggregation.

Sales QTD = TOTALQTD([Total Sales], Dates[Date])
DAXintermediate

Sales MTD = TOTALMTD()

Month-to-date aggregation.

Sales MTD = TOTALMTD([Total Sales], Dates[Date])
DAXintermediate

Sales SPLY = SAMEPERIODLASTYEAR()

Prior year comparison for variance analysis.

Sales Prior Year = CALCULATE([Total Sales], SAMEPERIODLASTYEAR(Dates[Date]))
DAXintermediate

YoY Growth = [Total] - [Prior Year]

Absolute variance compared to previous year.

YoY Growth = [Total Sales] - [Sales Prior Year]
DAXintermediate

YoY Growth % = DIVIDE(YoY, Prior)

Percentage growth rate compared to prior year.

YoY Growth % = DIVIDE([YoY Growth], [Sales Prior Year])
DAXadvanced

SUMX() Iterative Context

Compute row-by-row expressions before sum.

Revenue Gross = SUMX(Sales, Sales[Units] * Sales[UnitPrice])
DAXadvanced

Top 5 Deals = TOPN()

Filter table to the top N performers.

Top 5 Sales = CALCULATE([Total Sales], TOPN(5, Customers, [Total Sales]))
DAXadvanced

30-Day Rolling = DATESINPERIOD()

Rolling 30-day window aggregation.

Sales 30D = CALCULATE([Total Sales], DATESINPERIOD(Dates[Date], MAX(Dates[Date]), -30, DAY))
DAXadvanced

Customer Tier = IF(Nested)

Classify customers into Enterprise, Mid-Market, SMB.

Tier = IF([Total Sales] > 20000, "Enterprise", IF([Total Sales] > 5000, "Mid-Market", "SMB"))
DAXadvanced

Active Customers 90D

Count customers active within last 90 calendar days.

Active 90D = CALCULATE(DISTINCTCOUNT(Sales[CustomerID]), DATESINPERIOD(Dates[Date], LASTDATE(Dates[Date]), -90, DAY))
DAXadvanced

Churn Delta = Total - Active

Dormant accounts calculation.

Churned Accounts = [Total Customers] - [Active 90D]
DAXadvanced

ALLSELECTED() Dynamic Baseline

Compute ratio against currently sliced dashboard selections.

Selected Total = CALCULATE([Total Sales], ALLSELECTED(Sales))
DAXadvanced

RANKX() Product Ranking

Dense ranking of entities by metric value.

Rank = RANKX(ALL(Products[Category]), [Total Sales], , DESC, Dense)
DAXadvanced

MEDIANX() Iterative Median

Iterative median transaction evaluation.

Median Deal = MEDIANX(Sales, [Total Sales])
DAXadvanced

Cumulative % Pareto Curve

Pareto 80/20 distribution calculation.

Cumulative % = DIVIDE(CALCULATE([Total Sales], FILTER(ALL(Products), [Rank] <= EARLIER([Rank]))), [All Product Sales])
STATISTICSbasic

Two-Sample T-Test (scipy.stats)

Compare means between two independent groups for statistical significance.

stats.ttest_ind(group_a, group_b, equal_var=False)
STATISTICSintermediate

One-Way ANOVA (f_oneway)

Test if means across 3+ categories differ significantly.

stats.f_oneway(cat1, cat2, cat3)
STATISTICSbasic

Pearson Correlation (pearsonr)

Measure linear relationship strength and p-value between metrics.

stats.pearsonr(df['revenue'], df['profit'])
STATISTICSintermediate

Spearman Rank Correlation

Non-parametric monotonic correlation for non-normal distributions.

stats.spearmanr(df['revenue'], df['profit'])
STATISTICSintermediate

Chi-Square Test of Independence

Evaluate association between two categorical variables.

stats.chi2_contingency(contingency_table)
STATISTICSbasic

Shapiro-Wilk Normality Test

Test whether continuous distribution follows Gaussian distribution.

stats.shapiro(df['revenue'])
STATISTICSbasic

Z-Score Outlier Detection

Flag records deviating more than 3 standard deviations from mean.

stats.zscore(df['revenue'])
STATISTICSbasic

Interquartile Range (IQR) Filter

Identify outliers based on Q1 - 1.5*IQR and Q3 + 1.5*IQR bounds.

q25, q75 = np.percentile(x, [25, 75])
STATISTICSadvanced

OLS Linear Regression (statsmodels)

Fit ordinary least squares regression with summary R-squared & p-values.

sm.OLS(y, sm.add_constant(X)).fit()
STATISTICSadvanced

Mann-Whitney U Test

Non-parametric rank-sum test for two independent samples.

stats.mannwhitneyu(group_a, group_b)
PYSPARKbasic

spark.createDataFrame()

Instantiate PySpark distributed DataFrame from pandas or memory.

sdf = spark.createDataFrame(df)
PYSPARKbasic

groupBy().agg() Sum & Mean

Distributed group aggregation by dimension.

sdf.groupBy('region').agg(F.sum('revenue'))
PYSPARKadvanced

Window.partitionBy() Ranking

Analytical window partition ranking over partitioned keys.

F.rank().over(Window.partitionBy('region').orderBy(...))
PYSPARKbasic

F.when().otherwise() Case Statement

Conditional column logic expression.

F.when(F.col('profit') > 1000, 'High').otherwise('Low')
PYSPARKbasic

DataFrame.filter() Predicate

Filter rows matching multiple boolean conditions.

sdf.filter((F.col('revenue') > 5000) & (F.col('profit') > 1000))
PYSPARKintermediate

withColumn() Computed Metric

Derive new calculated column across distributed partitions.

sdf.withColumn('margin_pct', F.col('profit') / F.col('revenue') * 100)
PYSPARKintermediate

DataFrame.join() Broadcast

Join two Spark DataFrames with broadcast hash optimization.

sdf.join(F.broadcast(lookup_df), 'category', 'inner')
PYSPARKintermediate

spark.sql() Direct Query Engine

Register temporary view and query using Spark SQL dialect.

spark.sql('SELECT region, SUM(revenue) FROM sales GROUP BY 1')
PYSPARKadvanced

repartition() & coalesce()

Control distributed cluster partitioning across executor cores.

sdf.repartition(8, 'region')
PYSPARKadvanced

VectorAssembler ML Pipeline

Combine raw columns into ML feature vectors for PySpark MLlib.

VectorAssembler(inputCols=['revenue', 'profit'], outputCol='features')
HADOOPbasic

MapReduce Java Mapper

Extract key-value pairs in distributed Map phase.

public class SalesMapper extends Mapper<LongWritable, Text, Text, DoubleWritable>
HADOOPbasic

MapReduce Java Reducer

Aggregate values across keys in distributed Reduce phase.

public class SalesReducer extends Reducer<Text, DoubleWritable, Text, DoubleWritable>
HADOOPintermediate

HiveQL CREATE EXTERNAL TABLE

Define schema on top of HDFS storage files.

CREATE EXTERNAL TABLE sales (...) STORED AS PARQUET
HADOOPintermediate

Hive Partition Pruning

Query partitioned table with zero-overhead HDFS path pruning.

SELECT * FROM sales WHERE region = 'North America' AND fiscal_year = 2026
HADOOPadvanced

Hive Vectorized Execution

Enable SIMD CPU vectorized processing for 10x query throughput.

SET hive.vectorized.execution.enabled = true;
HADOOPbasic

HDFS CLI: hdfs dfs -ls

List files and directory sizes across HDFS clusters.

hdfs dfs -ls -h /user/dataquery/warehouse/
HADOOPbasic

HDFS CLI: hdfs dfs -put

Ingest local files into HDFS NameNode replicated blocks.

hdfs dfs -put local_sales.parquet /user/dataquery/landing/
HADOOPadvanced

YARN Application Telemetry

Inspect ResourceManager container allocations and queue metrics.

yarn application -status <app_id>
HADOOPadvanced

Hive CLUSTERED BY Bucketing

Hash bucket large tables for ultra-fast merge joins.

CLUSTERED BY (customer_id) INTO 16 BUCKETS
HADOOPintermediate

Hadoop Streaming (Python)

Run native Python mapper and reducer scripts over standard I/O.

hadoop jar hadoop-streaming.jar -mapper mapper.py -reducer reducer.py
MLbasic

Logistic Regression

Predicts the probability of a class label.

LogisticRegression(max_iter=1000)
MLbasic

Linear Regression

Predicts continuous values using a best-fit line.

LinearRegression().fit(X_train, y_train)
MLadvanced

Gradient Boosting

Builds models step by step to reduce errors.

GradientBoostingClassifier(n_estimators=100)
MLadvanced

Principal Component Analysis (PCA)

Reduces dimensions while keeping key patterns.

PCA(n_components=2).fit_transform(X)
MLbasic

Decision Tree

Splits data into branches to make decisions.

DecisionTreeClassifier(max_depth=5)
MLadvanced

Neural Networks (MLP)

Learns complex patterns through connected layers.

MLPClassifier(hidden_layer_sizes=(64, 32))
MLbasic

Random Forest

Combines many trees to improve prediction accuracy.

RandomForestClassifier(n_estimators=100)
MLintermediate

DBSCAN

Finds dense clusters and detects outliers.

DBSCAN(eps=0.5, min_samples=3)
MLintermediate

Support Vector Machine (SVM)

Finds the best boundary between classes.

SVC(kernel='rbf', C=1.0)
MLintermediate

Hierarchical Clustering

Builds clusters in a tree-like hierarchy.

AgglomerativeClustering(n_clusters=3)
MLbasic

Naive Bayes

Uses probabilities to classify data quickly.

GaussianNB()
MLadvanced

ARIMA

Forecasts time-series data using past trends.

ARIMA(ts, order=(1,1,1)).fit()
MLbasic

K-Nearest Neighbors (KNN)

Classifies using the labels of nearby points.

KNeighborsClassifier(n_neighbors=5)
MLbasic

K-Means Clustering

Groups similar data into K clusters.

KMeans(n_clusters=3)
MLintermediate

Apriori

Finds frequent item patterns and association rules.

apriori(basket, min_support=0.05)
MLadvanced

XGBOOST

A fast boosting method for strong predictive performance.

xgb.XGBClassifier(n_estimators=100)
MLbasic

train_test_split (80/20)

Partition features and target into reproducible training and testing sets.

train_test_split(X, y, test_size=0.2)
MLintermediate

Confusion Matrix & Heatmap

Analyze True Positives, False Positives, True Negatives, and False Negatives.

confusion_matrix(y_test, preds)