0

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A for administrators, end users, developers and designers for Craft CMS

/*
On the right hand side of the window there's a box 
 It says "Database Schema" 
 You can click the names of the tables (like Posts) to collapse them
 When you run a query, it may ask for a captcha
 If you return multiple results, it will make you click the Results box to show them 
 

Don't worry if you dont get it exactly or cant remember the exact syntax.
There are often multiple correct answers depending on a schema.
Talk through your thought process when you're confused.
You're not expected to finish.

*/
--- return 1 row from Posts
select top 1 *
from dbo.Posts

--- return the 1 row from VoteTypes with the biggest Id
select top 1 *
from dbo.VoteTypes
order by Id desc

-- return the count of all the Votes that where created in 2019
select count(*) as vote_count
from dbo.Votes as v
where 
v. creationdate >= '2019-01-01' 
and v.creationdate < '2020-01-01'

-- create a temp table named post_count
-- it needs to store the count of posts by user
-- return the first row from the temp table
drop table if exists #post_count

select count(*) as user_count, p.OwnerUserID
into #post_count
from dbo.Posts as p
group by p.OwnerUserID

create table #post_count1 ( user_id int, post_count int ) 
insert #post_count1 ( user_id, post_count) 
select p.OwnerUserID,
count(*)
from dbo.Posts as p
group by p.OwnerUserID

select top 1 * 
from #post_count1

select top 1 *
from #post_count

fork query

edited aug 20

created aug 20 anonymous