How to get a count of number of nodes created per week in views and show the week

Question

I am creating a view to breakdown the number of node published per week using views. I were able to create a view that will show me the breakdown day or month but there is no option for week. How will I do that?

and the result

Also how do I make it show the first day of that week or the first item publish for that week.

Any help is appreciated.

** update**

I were able to get the right mysql query:

SELECT COUNT(DISTINCT node.nid) AS nid, MIN(node.nid) AS nid_1, 'node' AS field_data_field_published_at_node_entity_type, YEARWEEK(DATE_FORMAT((DATE_ADD('19700101', INTERVAL node.created SECOND) + INTERVAL -14400 SECOND), '%Y%m%d')) AS node_created_yearweek FROM node LEFT JOIN field_data_field_published_at field_data_field_published_at ON node.nid = field_data_field_published_at.entity_id AND field_data_field_published_at.entity_type = 'node' WHERE (( (node.status = '1') )) GROUP BY field_data_field_published_at_node_entity_type, node_created_yearweek ORDER BY node_created_yearweek DESC LIMIT 55;   

How can I add the query to the view?

update 2 base on you answer it is grouping by day showing week:

update 3

and the list is still the same grouping by day showing week.
The numbers in parenthesis are the number of item published that/a day of the week.
Example on week 21 we published item 6 days in that week and in the parenthesis is the number of item published for each day.

upate 4 some progress
removed sort criteria

expected output but all years (last 3 years) are combine

Answer 1

Contextual Views Summary

You can show weekly results using a Contextual filter.

In D7 you can add a new Contextual filter Content: Created week and configure it with these options:
When the filter value is NOT available
Display a summary
Sort by Date
Format List
Base path (you must enter the Page path if this isn't the first Page Display in your Views)
Display record count with link
Override number of items to display to 0 to have an infinite number of weeks show. Use this if you want the Pager to control the number of nodes shown for each week (if there are many). Otherwise, the Pager will apply to the number of weeks as well as nodes!

This way you get a list of weeks by their number, each week links through to a list of nodes created during that week:

The Sorting criteria you add to the Views will affect the list of nodes you see after you click through a summary week link. If you add the Content: post date criteria, the nodes will show as the oldest first if you set it to ascending.

---

Aggregation

For more control, you can use Aggregation to count the results, but it involves a lot more steps to recreate the Summary like above.

After you turn on Aggregation simply change one of the Fields that is unique (for example Node ID) to Count DISTINCT and it will show the number of results.

You'd have to combine it somehow with the Contextual weekly filter if you wanted to display something different from the Summary, possibly create one Views display for the first node of the week, then in an Attachment do the counting and attach it to the week.

Adding a set query to Views is a bit of an overkill since the purpose of Views is to build queries (through an interface).