-4

I am in a test and there is the next questions,

You are have 3 tables

First, 

Users - Userid, Name, Email, Phone

Second, 

Ads - Adid, Userid, name, title, body, url, creationdate

Third - 

Analystions - Adid, Date, Impressions, Clicks, DailySpent,

Now, they ask me that -

Take all the ads that the user "123456" Created and order then by the last creation date and to the first.

Next - Take the AVG From CTR=Clicks/Impressions, from the ad "123456"

Thanks so much for who that helped,

Alon M
  • 1,583
  • 7
  • 30
  • 44
  • 3
    dont ask people to do your homework for you, it's bad manners ;) – Tristan Jul 14 '14 at 12:08
  • What have you tried? This looks like a basic select statement, then a simple group by. – Daniel E. Jul 14 '14 at 12:08
  • I already got how to do the orderby, i just dont have any idea how to do the avg part, i need to get the avg from clicks/impressions from adid = 123456 – Alon M Jul 14 '14 at 12:15

1 Answers1

1

To answer the question as you asked it in the title, you would use the "order by Date desc" clause in order to show the most recent ads first. Omit the "desc" to show ads by oldest first.

That last part looks like you need an inner join. inner join to your Ads table, and then to your "Analystions" table

Eric
  • 930
  • 1
  • 6
  • 14
  • i need to get an avg to clicks/impressions from the table ads in adid(which is the ad number 123456) – Alon M Jul 14 '14 at 12:14
  • once you joined your tables, select avg(clicks), avg(impressions), userid and then add a group by clause and group it by userId. something like "group by userid" – Eric Jul 14 '14 at 12:18
  • see this question http://stackoverflow.com/questions/10702546/sql-query-with-avg-and-group-by – Eric Jul 14 '14 at 12:22