I have two tables in MS SQL:

```
Category
 - Id (int)

CategoryElement
 - Id (int)
 - CategoryId (int) //References Category.Id
 - Time (datetime)

```

So, each category can have zero or more category elements. Time, in CategoryElement, represents the time when the category element was created.

I need some help writing the following query: order categories descending by the number of category elements added in the last 7 days and display category id and how many elements were added.

So far I managed to write they query without the "added in the last 7 days" part:

```
SELECT c.Id, e.ElemCount
FROM dbo.Categories AS c 
INNER JOIN
    (SELECT CategoryId, COUNT(1) AS ElemCount, MAX(Time) AS LatestElem
     FROM dbo.CategoryElements
     GROUP BY CategoryId) AS e 
ON c.ID = e.CategoryId
ORDER BY e.LatestElem DESC

```