You don't say what you want to do with the results, but you can certainly SELECT the user's earliest ten bids of each day:
with ProductBidsRanked(productID, userID, Created, rk) as (
select
productID, userID, Created,
row_number() over (
partition by userID, dateadd(datediff(day,0,Created),0)
order by Created
)
)
select productID, userID, Created
from ProductBidsRanked
where rk <= 10
Of course, if you only need the total, and want to replace the total with 10 when it exceeds 10, that's easier:
with PartialAgg(userID,countOr10) as (
select
userID,
case when count(*) > 10 then 10 else count(*) end
from ProductsBids
group by userID, dateadd(datediff(day,0,Created),0)
)
select
userID, sum(countOr10) as BidsAdjusted
from PartialAgg
group by userID;
Response to comment:
You say you want to add it to the user's bidcount, but bidcount isn't a column name in any of your tables. Perhaps you meant TOTALBIDS, so for example, if the second query is the one that works for you, you could do something like this:
with PartialAgg(userID,countOr10) as (
select
userID,
case when count(*) > 10 then 10 else count(*) end
from ProductsBids
group by userID, dateadd(datediff(day,0,Created),0)
), FullAgg(userID,BidsAdjusted) as (
select
userID, sum(countOr10) as BidsAdjusted
from PartialAgg
group by userID
)
update users set
TOTALBIDS = TOTALBIDS + BidsAdjusted
from users join FullAgg
on FullAgg.userID = users.userID
FYI, there's some SQL Server specific stuff here - ANSI doesn't allow UPDATE with a CTE, and I didn't confirm that T-SQL's quirky UPDATE .. FROM can be used in combination with a CTE.
In any case, given that this seems like the kind of update you would run only infrequently, and never concurrently, it could be wisest to insert the results of my first suggestion (whichever serves your purpose) into a temporary table and base your update on that.