Here is a solution based on the Window.Closed event which has an Handled property we can use.:

In MainWindow.cpp:

namespace winrt::WinUIApp1CPP::implementation
{
    MainWindow::MainWindow()
    {
        InitializeComponent();

        _closing = false;
        Closed([&](IInspectable const&, WindowEventArgs const& e)
            {
                if (!_closing)
                {
                    _closing = true;
                    e.Handled(true);

                    ContentDialog dialog;
                    dialog.XamlRoot(Content().XamlRoot());
                    dialog.Title(box_value(L"Do you really want to close the app?"));
                    dialog.PrimaryButtonText(L"Yes, close");
                    dialog.CloseButtonText(L"No, cancel");
                    dialog.DefaultButton(ContentDialogButton::Close);
                    dialog.PrimaryButtonClick([&](auto&& ...)
                        {
                            DispatcherQueue().TryEnqueue([&](auto&& ...)
                                {
                                    Close();
                                });
                        });

                    dialog.ShowAsync().Completed([&](auto&& ...)
                        {
                            _closing = false;
                        });
                }
            });
    }
}

With MainWindow.h:

namespace winrt::WinUIApp1CPP::implementation
{
    struct MainWindow : MainWindowT<MainWindow>
    {
        MainWindow();
        ...
        bool _closing;
        ...
   };
}

And for what it's worth, the equivalent in C# (MainWindow.xaml.cs):

public MainWindow()
{
    InitializeComponent();

    var closing = false;
    Closed += async (s, e) =>
    {
        if (!closing)
        {
            closing = true;
            e.Handled = true;

            var dialog = new ContentDialog();
            dialog.XamlRoot = Content.XamlRoot;
            dialog.Title = "Do you really want to close the app?";
            dialog.PrimaryButtonText = "Yes, close";
            dialog.CloseButtonText = "No, cancel";
            dialog.DefaultButton = ContentDialogButton.Close;
            dialog.PrimaryButtonClick += (s, e) => DispatcherQueue.TryEnqueue(Close);
            var result = await dialog.ShowAsync();
            closing = false;
        }
    };
}

Note: the usage of DispatcherQueue.TryEnqueue should not be necessary but without it, the Close() call currently causes a crash in WinUI3...