Note: I'm going to answer the technical question as asked here. Depending on the role the interview is for, the correct interview answer may very well be "having a counter is stupid; there should be a time constraint or per-account limitation" (as Bart von Ingen Schenua appears to suggest in the comments).
Assumption: 500.000 isn't the exact maximum; 500.013 usages wouldn't be a problem either.
The issue with a simple
UPDATE coupons SET usageCount = usageCount + 1 WHERE id = 21 is that your database will start to have locking issues, because multiple processes will want to lock the same record at the same time.
The way to prevent this is to not update
coupons every time the coupon is used. Multiple strategies are available.
Log usages, periodically update
Log every usage of a coupon into
INSERT INTO coupon_usages (coupon_id, order_id VALUES (12, 34534534)). In a different process periodically do a
COUNT() over that data and use that to update
Update in a different datastore, copy over value periodically
Instead of updating the counter in the database, keep the counter in something like Redis. Like before, have a different process to periodically read Redis (or whatever you decide to use) and update
coupons based on that data.
Keep a counter in memory
If you have an application server with persistent objects, keep a counter there. Update the database in a separate thread.
Only update some of the time
Instead of increasing
coupons with 1 every time the coupon is used, increase
coupons with 1000
if (rand(0, 999) === 0).
That last one is a "I have no other options" option; it will give you an inaccurate count, due to the non-perfect distribution of random number generators.
These options should be enough to answer the technical side of the interview question. I wouldn't throw all of them out there at the same time. Pick something, discuss its pro's and cons, then mention another option to counter specific cons of the first option, etc.