Three critical components for data analysis and how SQL can realize these components

An analysis can help business owners or commercial leaders understand their current position and further derive strategies to modify pace, improve execution, or stop working out in the wrong direction. An analysis fundamentally contains three components: Category, Time, and Value. No exception.

Category

Common categories are region, country, district, location, product category, gender, age, and anything you may find on financial reports. Categories are basically what we want to compare in between each other. The standard targets are ad-hoc, such as to compare different consuming values, i.e., different products, different age groups, and different treatments.

Echo to SQL, the categories would be the most frequently used columns in ‘group by,’ ‘order by,’ or ‘partition by.’ Our business goals, deep-dive into the root, are to reduce cost or increase revenue. The category is how we find a piece to improve in complex business worlds.

Time

Time components, increasing by the time you run a business or collected from external reports, is a way to see your business changes. Frequently, we see MoM, QoQ, seven days vs. 14 days vs. 21 days. These kinds of comparisons are it works.

Echo to SQL, the standard functions are Extract(), Next_Day(), Last_Day(), and DATE_TRUNC(). If your data set is clean, you can leverage LAG() and LEAD() to generate current, previous, and subsequent values automatically over time.

Value

We often track common values as KPIs or metrics in business reports—for example, exploration rate, engagement, activation, retention, and attrition.

We stored pure values in the database and built tables for compound values or leveraged DAX at the BI level to create compound values. According to SQL, the functions we use pretty often for values are aggregation functions such as add(), sum(), max(), min(), avg(), and count(). And together with distinct().

When delivering analyses

In analyses, we fix a category to see how this category changes its value over time. Or we fix time to compare values among different categories. If the time and category shift together, it usually represents the macro trend and can help us identify our current position. Remember that what we provide is better with a 360 analysis, including category or time. If we only do partial research and trust the results, we may miss some hidden points and guide the team differently.