What is SQL

Structured Query Language (SQL) is a powerful tool used to manage and retrieve data from relational databases. In the lending space, SQL plays a vital role in handling your customer's data, generating reports, and ensuring data integrity.

Understanding SQL Structure

An SQL query consists of several components that work together to get specific data. Let's explore the structure of an SQL query.

SELECT [columns]
FROM [table]
WHERE [conditions]
GROUP BY [columns]
HAVING [conditions]
ORDER BY [columns]
LIMIT [count]

Meaning of the SQL components

SELECT: Specifies the columns you want to retrieve from the table.
FROM: Specifies the table from which you want to retrieve data.
WHERE: Filters rows based on specified conditions.
GROUP BY: Groups the result set by one or more columns.
HAVING: Filters the grouped rows based on conditions.
ORDER BY: Sorts the result set by specified columns.
LIMIT: Limits the number of rows returned.

That is the general SQL composition, but the components will depend on the data you are interested in and the complexity of the report you want to get. Now let's look at one real example from Canopy.

Total Origination

Total origination will show you the total origination cost of all Loans (Installment) and Charges (Revolving) from specific dates.

SELECT li.line_item_type AS lending_product_type,
       sum(li.original_amount_cents) as total_USD_amount_originated
FROM data_direct.line_items li
WHERE (li.line_item_type = 'LOAN') OR (li.line_item_type = 'CHARGE')
AND ((li.line_item_status = 'VALID'))
AND (((li.effective_at) >= ((DATE_TRUNC('month', (DATE_TRUNC('month', CURRENT_DATE))) + INTERVAL '-1 month')::timestamp)
AND ((li.effective_at) < ((DATE_TRUNC('month', (DATE_TRUNC('month', CURRENT_DATE))) + INTERVAL '-1 month')::timestamp + '1 month'::interval))))
GROUP BY 1
ORDER BY total_USD_amount_originated DESC;

The table above is the latest output of the query. Each column is outlined within the SELECT statement (lines 1-2), whilst FROMclause (line 3) refers to the DataDirect table we are pulling records from. As for rows of data returned, the filters are defined in the WHERE clause (lines 4-7), whilstGROUPED BY statement summarizes those rows by the first column (line 8). TheORDER BY keyword helps us to sort the rows in descending order (line 9).

Using Query Tools

We recommend using user-friendly user tools, like Trevor to write queries and preview the data.