imaginglkp.blogg.se

GROUP BY EXTRACT(YEAR FROM date), EXTRACT(QUARTER FROM date) To achieve that, I’ll use the table card_transaction and this code: In this example, I’ll group data by years and quarters and show the number of transactions. months, weeks, or days, depending on the data you have). Learning this principle will allow you to group data on any other level (i.e. You’ll certainly need to group data into years and quarters if you’re creating financial reports. Now that you’re familiar with the database, let’s move on to the examples! 6 Examples of Advanced SQL Queries for Financial Analysis 1: Group Data by Year and Quarterįinancial data usually needs to be grouped in specific time periods or time buckets. card_number_id – The card’s ID a foreign key (FK) that references the table card_number.amount – The transaction amount, in dollars.id – The transaction’s ID the primary key (PK).The last is the card_transaction table, which has these columns: card_type_id – The card type’s ID a foreign key (FK) that references the table card_type.

customer_id – The customer’s ID a foreign key (FK) that references the table customer.id – The card’s ID the primary key (PK).Next comes the card_number table with the attributes: country_id – The country’s ID this is a foreign key (FK) that references the table country.first_name – The customer’s first name.NIN – The customer’s national identification number.id – The customer’s ID the primary key (PK).The table customer consists of the columns: id – The card type’s ID the primary key (PK).The next table is card_type, with these attributes: country_name – The name of the country.id – The country’s ID and the primary key (PK) of this table.The table country has the following attributes: Now, let’s get into the details of each table. We’ll imagine that this is the database of a credit card processing company and you’re their go-to employee when it comes to analyzing financial data. Introducing the DatabaseĪll six examples will use this database, which consists of five tables: The SQL knowledge level required in this article – such as window functions, GROUP BY extensions, and recursive queries – is covered in the Advanced SQL track.