SQL query on Top 5 US Sales

This SQL query retrieves the top 5 product subcategories by sales amount in the United States, using the US Dollar as the currency.

Below is the break down of the Query.

SELECT statement specifies the columns to be returned in the result set. It selects the English product subcategory name (dpsc.EnglishProductSubcategoryName) and the sum of sales amounts (SUM(fs.SalesAmount)), which is aliased as SalesAmount.

The FROM clause defines the tables involved in the query. The query retrieves data from the FactInternetSales table, joining it with other tables: DimProduct (aliased as dp), DimProductSubcategory (aliased as dpsc), DimCurrency (aliased as dcy), and DimSalesTerritory (aliased as dst).

The JOIN conditions connect the tables based on specific keys. The FactInternetSales.ProductKey is joined with DimProduct.ProductKey, DimProduct.ProductSubcategoryKey is joined with DimProductSubcategory.ProductSubcategoryKey, FactInternetSales.CurrencyKey is joined with DimCurrency.CurrencyKey, and FactInternetSales.SalesTerritoryKey is joined with DimSalesTerritory.SalesTerritoryKey.

The WHERE clause filters the data based on specific conditions. It specifies that the sales territory country (dst.SalesTerritoryCountry) must be 'United States', and the currency name (dcy.CurrencyName) must be 'US Dollar'.

The GROUP BY clause groups the result set by the English product subcategory name (dpsc.EnglishProductSubcategoryName).

The ORDER BY clause sorts the result set in descending order based on the sales amount (SalesAmount).

Finally, the TOP(5) clause limits the result set to the top 5 rows.

In summary, the query retrieves the top 5 product subcategories by sales amount in the United States, sorted in descending order of sales amount. It calculates the sales amount by summing the sales amounts from the FactInternetSales table, considering the join conditions and filtering based on the sales territory country and currency.