Writing SELECT Queries with Calculated Fields, Aliases, and Sorting

Hello, and welcome. This intensive course develops the practical workflow behind a Snowflake-focused analyst and QA role: write trustworthy SQL, validate results independently, build KPI reporting in Power BI, and then turn that work into three portfolio projects. We begin with the small skills that make every later task safer: selecting exactly the fields requested, making calculations readable, and returning results in a deliberate order.

You do not need a Snowflake account for today. The SQL patterns are portable, and we will start using Snowflake-style fully qualified table names so the transition on Day 3 is straightforward.

By the end of this lesson, you should be able to turn a reporting request such as “show each order’s region, product, and revenue, with highest revenue first” into a clear SELECT query.

---

The shape of a focused query

A SQL query is a request for a result set: a grid of rows and columns. For this lesson, its core form is:

SELECT
    column_name,
    another_column_name
FROM database_name.schema_name.table_name
ORDER BY column_name ASC;

Each clause has a distinct responsibility:

Clause	Purpose
SELECT	Defines the columns and calculated fields shown in the result.
FROM	Identifies the source table or view.
ORDER BY	Defines the presentation order of returned rows.

The SQL is written with SELECT first because that states the desired output. Conceptually, the database locates the source data in FROM, produces the requested output fields, then sorts the result using ORDER BY.

For an analyst, the difference between an exploratory query and a reporting query matters:

SELECT *
FROM PORTFOLIO_DB.RAW.SALES_ORDER;

SELECT * is useful for a quick inspection of an unfamiliar table. It returns all available columns, which helps you understand names and data types. But it is usually a poor final reporting query because:

• the result contains irrelevant fields;
• the report can change unexpectedly if the table later gains columns;
• readers cannot easily see which fields are intentionally part of the metric or report.

A reporting query should name the columns required by the business question.

---

Select only what the report needs

Suppose SALES_ORDER contains these columns:

ORDER_ID
ORDER_DATE
REGION
PRODUCT_NAME
QUANTITY
UNIT_PRICE
SALES_REP_EMAIL
INTERNAL_LOAD_TIMESTAMP

A sales manager asks for an order-level export containing only the order identifier, date, region, product, quantity, and unit price. The query is:

SELECT
    order_id,
    order_date,
    region,
    product_name,
    quantity,
    unit_price
FROM PORTFOLIO_DB.RAW.SALES_ORDER;

Notice two useful conventions:

1. One selected item per line. This makes it easy to review, add, remove, or compare fields against a specification.
2. The selected sequence is the output sequence. Put identifying fields first, dimensions next, and measures or calculations after them. This makes results more readable when exported or used for QA.

The source is written as:

PORTFOLIO_DB.RAW.SALES_ORDER

In Snowflake, this means:

database.schema.object

Later, you may have a worksheet context that allows FROM SALES_ORDER. But fully qualifying important production, QA, or portfolio queries reduces ambiguity: someone reviewing the SQL can see exactly which object was intended.

A common syntax error

This is invalid because SQL expects another selected item after the comma:

SELECT
    order_id,
    order_date,
FROM PORTFOLIO_DB.RAW.SALES_ORDER;

The final item in the SELECT list should not have a trailing comma.

---

Calculated fields: create the measure at query time

A calculated field is an expression evaluated when the query runs. It does not add or alter a column in the underlying table.

For individual sales-order rows, gross revenue is commonly calculated as:

quantity * unit_price

You can place that expression directly in SELECT:

SELECT
    order_id,
    product_name,
    quantity,
    unit_price,
    quantity * unit_price
FROM PORTFOLIO_DB.RAW.SALES_ORDER;

This works, but the calculated output column may receive an awkward system-generated heading. In reporting and QA work, an unclear heading creates avoidable ambiguity. Use an alias to state what the calculation means.

SELECT
    order_id,
    product_name,
    quantity,
    unit_price,
    quantity * unit_price AS gross_revenue
FROM PORTFOLIO_DB.RAW.SALES_ORDER;

AS gross_revenue gives the result column a meaningful name. It does not rename a table column and does not persist beyond this query.

Aliases are valuable for two separate reasons:

• Communication: gross_revenue is clearer than quantity * unit_price.
• Reusability in the result: in Snowflake, you can use a SELECT alias in ORDER BY, avoiding repeated expressions.

You can alias ordinary fields as well:

SELECT
    order_id AS sales_order_id,
    order_date AS sale_date,
    region AS sales_region,
    quantity * unit_price AS gross_revenue
FROM PORTFOLIO_DB.RAW.SALES_ORDER;

For now, use short, descriptive aliases with underscores. They work cleanly in SQL, Power BI field lists, screenshots, and future project documentation.

Read the business meaning, not just the arithmetic

A calculation can be syntactically valid and still be wrong for the business definition. For example:

quantity * unit_price AS gross_revenue

is appropriate only if UNIT_PRICE is the price per unit and no discount, tax, return, or currency conversion belongs in the required definition. In later QA lessons, you will test such assumptions against a KPI specification. Today, develop the habit of naming calculations so their meaning can be checked.

---

Sorting is part of the requirement

Without ORDER BY, a database is free to return rows in any order. The current result may appear stable, but storage and execution details can change. Never claim “highest revenue first” unless the query explicitly requests that order.

To sort from largest to smallest:

ORDER BY gross_revenue DESC;

To sort from smallest to largest:

ORDER BY gross_revenue ASC;

ASC means ascending and DESC means descending. While ascending is commonly the default, stating the direction explicitly makes the query easier to review.

Here is a complete, reporting-ready order extract:

SELECT
    order_id AS sales_order_id,
    order_date AS sale_date,
    region AS sales_region,
    product_name,
    quantity,
    unit_price,
    quantity * unit_price AS gross_revenue
FROM PORTFOLIO_DB.RAW.SALES_ORDER
ORDER BY
    gross_revenue DESC,
    sale_date DESC,
    sales_order_id ASC;

Read this ORDER BY clause in priority order:

1. Sort every order by gross_revenue, putting the highest values first.
2. If two orders have the same revenue, place the more recent sale_date first.
3. If both revenue and date match, sort by sales_order_id ascending.

The second and third sort expressions are tie-breakers. They make an extract consistent and easier to compare across refreshes. This is particularly important in QA: two query outputs can contain exactly the same values but look different if their tie ordering is not controlled.

A useful rule is:

If the user, dashboard, export, or test expects a particular order, put every required ordering rule in ORDER BY.

Do not sort only by gross_revenue if the specification says “by region alphabetically, then highest revenue within each region.” The priority must match the wording:

SELECT
    region,
    product_name,
    quantity * unit_price AS gross_revenue
FROM PORTFOLIO_DB.RAW.SALES_ORDER
ORDER BY
    region ASC,
    gross_revenue DESC,
    product_name ASC;

This does not put the highest-revenue order globally at the top. It first creates alphabetical region groups; revenue is sorted only within each region. The placement of expressions in ORDER BY changes the meaning.

---

A practical writing and review routine

When turning a request into a query, use this short sequence before you run it:

1. Identify the row grain. Ask what one output row represents. In the examples, it is one sales order.
2. List requested dimensions and identifiers. Such as date, region, product, and order ID.
3. Write calculations in plain language first. For example, “gross revenue equals quantity times unit price.”
4. Give every calculated field a descriptive alias.
5. Translate order words precisely. “Highest first” requires DESC; “alphabetical” normally requires ASC.
6. Add tie-breakers when repeatable order matters.
7. Review the output labels and sequence as if they were fields in a report export.

For a five-minute coding routine, type the complete SALES_ORDER query above into a SQL editor, then make these mechanical edits one at a time:

• reorder the selected fields and observe that the output columns move;
• change gross_revenue DESC to ASC and observe that the ranking reverses;
• temporarily remove ORDER BY, then recognize that the displayed row sequence has no guaranteed business meaning;
• restore the full multi-column ordering before saving the query.

This type of controlled edit is a simple but effective QA habit: change one condition, observe one consequence, and restore a known baseline.

---

Key takeaways

A strong foundational reporting query has four visible qualities:

• SELECT lists only the required columns, in a useful output sequence.
• Calculations such as quantity * unit_price create fields at query time.
• AS gives raw or calculated output fields unambiguous, reader-friendly names.
• ORDER BY makes the row sequence intentional; multiple sort fields establish priority and tie-breakers.

You also saw Snowflake’s database.schema.object naming pattern, which will become central once you begin working in Snowsight.

Next, you will add WHERE conditions to these queries so you can return only the rows that meet business criteria while keeping calculation and sorting logic clear.