You could technically do this with a lot of custom SQL, but it would be messy. I think your best bet would be to create a custom Date field on your categories called “Last Used”, and then to write a module that keeps its value updated as entries are saved.
The default Craft 3 project comes with a module file out of the box, so you can use that as a starting point.
First, add this to the top of the module file, after the namespace
line:
use Craft;
use craft\elements\Category;
use craft\elements\Entry;
use craft\events\ModelEvent;
use yii\base\Event;
Then in its init()
method, add this after the parent::init()
line:
Event::on(Entry::class, Entry::EVENT_AFTER_SAVE, function(ModelEvent $event) {
/** @var Entry $entry */
$entry = $event->sender;
if ($entry->enabled) {
// Loop through the related categories
foreach ($entry->myCategoriesField as $category) {
/** @var Category $category */
// Is this entry's post date more recent than the current Last Used value?
if ($entry->postDate > $category->lastUsed) {
$category->setFieldValue('lastUsed', $entry->postDate);
Craft::$app->elements->saveElement($category, false);
}
}
}
});
You can get Craft to re-save all of your entries by re-saving each of your sections’ settings. (You don’t need to change anything.)
With that in place, you can start sorting by that custom field in your templates.
{% set categories = craft.categories()
.group('myCategoryGroup')
.lastUsed('not :empty:')
.orderBy('lastUsed desc')
.limit(4)
.all() %}