How would you solve this? Teradata, AQL, Query.

Analytics

How would you solve this? Teradata, AQL, Query.

Hi, I have this prompt that requires a query to be written. 

 

If I posted in the wrong forum topic, first timer to all of this.

 

"Finally, in the executives responsible for footwear & accessories want to compare the sales performance of two popular brands: Nine West and Nina. Devise another query to compare (state by state) total revenue from the two brands in Dillard’s stores. These totals should be aggregated and broken out by state. Your result should be a table listing all states with Dillard’s stores, and total Nina sales amounts for all 29 states, then Nine West sales amounts for all 29 states. In other words, the top 29 rows should show Nina sales, and the next 29 Nine West sales. In the database, the brands are identified as NINA FOO and NINE WES. As with the other queries, be sure to include Purchases only (not returns).

The query should produce four columns: State, Brand, Total Revenue and Number of Stores. Please list the states in alphabetical order. Your query should generate 58 rows. Please show all of them."

 

The database is structured the way it is a screenshot. I have no idea how to start. This is very urgent and any answer is appreciated. I am so lost, dont know how to start. 

DB2.PNG

DB1.PNG

  • SQL
  • Teradata

Accepted Solutions
Junior Contributor

Re: How would you solve this? Teradata, AQL, Query.

There's no store column in skuinfo, you probably join on SKU (you need to know about Primary/Foreign Keys)

 

Like this  (the INNER keyword is optional):

FROM strinfo JOIN trnsact
  ON (strinfo.store = trnsact.store)
JOIN skuinfo 
  ON (trnsact.SKU = skuinfo.SKU)

And there's no need for LIKE:

WHERE skuinfo.brand IN (‘NINA FOO','NINE WES’)

 

1 ACCEPTED SOLUTION
6 REPLIES
Junior Contributor

Re: How would you solve this? Teradata, AQL, Query.

This is the third question here & on StackOverflow based on Dillard's, is your professor actually that bad at teaching basics?

 

Join + Where + Group By + Qualify Rank.

 

Could you show what you have tried to solve this homework assignment?

Re: How would you solve this? Teradata, AQL, Query.

Hi, thank you for replying. 

 

I am trying to go with the suggestion you gave me.

 

How do I only pull only these two brands from the brand col?

Is there any sort of count feature to count how many stores the brand was sold in, in a particular state?

How do I order the states alphabetically?

 

SELECT strinfo.state, skuinfo.brand, SUM(trnsact.amt), strinfo.store 

FROM trnsact, strinfo, skuinfo 

WHERE 
      strinfo JOIN trnsact
      ON (strinfo.store = trnsact.store)

GROUP BY strinfo.state,
         skuinfo.brand

ORDER BY strinfo.state

This is what I have so far.

 

Junior Contributor

Re: How would you solve this? Teradata, AQL, Query.

How do I only pull only these two brands from the brand col?

By adding a WHERE condition: Brand IN ('...','...')

 

Is there any sort of count feature to count how many stores the brand was sold in, in a particular state?

COUNT (DISTINCT storeid)

 

How do I order the states alphabetically?

ORDER BY Brand, State

 

Sorry, no need for QUALIFY as you seem to have exactly 29 states.

 

There's a join between  trnsact and skuinfo missing. 


 

 

Re: How would you solve this? Teradata, AQL, Query.

SELECT strinfo.state, skuinfo.brand, SUM(trnsact.amt), COUNT(DISTINC strinfo.store) 

FROM  skuinfo, 
              strinfo INNER JOIN trnsact
              ON (strinfo.store = trnsact.store)
              AND trnsact INNER JOIN skuinfo 
                        ON  (trnsact.store = skuinfo.store)

WHERE skuinfo.brand LIKE ‘%NINA FOO% AND %NINE WES%’

GROUP BY strinfo.state, skuinfo.brand

ORDER BY skuinfo.brand, strinfo.state

Does this seem to be correct?

Did I use the Join correctly?

Re: How would you solve this? Teradata, AQL, Query.

I am getting a error code.

 

Code = 3707: Syntax error, expected something like a 'SUCCEEDS' keyword or a 'MEETS' keyword or a 'PRECEDES' keyword or an 'IN' keyword or a 'CONTAINS' keyword between the word 'trnsact' and the 'INNER'

 

Junior Contributor

Re: How would you solve this? Teradata, AQL, Query.

There's no store column in skuinfo, you probably join on SKU (you need to know about Primary/Foreign Keys)

 

Like this  (the INNER keyword is optional):

FROM strinfo JOIN trnsact
  ON (strinfo.store = trnsact.store)
JOIN skuinfo 
  ON (trnsact.SKU = skuinfo.SKU)

And there's no need for LIKE:

WHERE skuinfo.brand IN (‘NINA FOO','NINE WES’)